25973956313

Committed 16 May 2026 09:55PM UTC coverage: 89.978%. Remained the same

Build # 25973956313

Build Type

push

github

Committed by

Sarcastic-Sunflower

Commit Message

Merge branch 'main' of github.com:witseie-elen4010/2026-group-lab-002

Coverage Stats

666 of 794 branches covered (83.88%)

Branch coverage included in aggregate %.

48 of 65 new or added lines in 15 files covered. (73.85%)

38 existing lines in 9 files now uncovered.

1417 of 1521 relevant lines covered (93.16%)

13.52 hits per line

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

96.0

/src/controllers/admin-activity-log-controller.js

const db = require('../../database/db');
const { getEventLabel, getCategory, getStatus, resolveActorFallback, CATEGORY_ACTIONS } = require('../services/activity-log-helpers');

const PAGE_SIZE = 20;
const ADMIN_CRUD_ACTIONS = new Set(['ADMIN_USER_ADD', 'ADMIN_USER_EDIT', 'ADMIN_USER_DELETE']);

const getFailedLoginCount = () =>
  db.prepare(`
    SELECT COUNT(*) as n FROM activity_log al
    JOIN actions a ON al.action_id = a.action_id
    WHERE a.action_name = 'AUTH_FAILED_LOGIN'
  `).get().n;

const fetchAuditData = (userId, createdAt) =>
  db.prepare(`
    SELECT old_data, new_data FROM admin_audit_log
    WHERE admin_id = ?
    ORDER BY ABS(strftime('%s', timestamp) - strftime('%s', ?))
    LIMIT 1
  `).get(userId, createdAt);

const BASE_FROM = `
  FROM activity_log al
  JOIN actions a ON al.action_id = a.action_id
  LEFT JOIN students st  ON al.user_id = CAST(st.student_number AS TEXT)
  LEFT JOIN staff    stf ON al.user_id = stf.staff_number
  LEFT JOIN admins   adm ON al.user_id = adm.admin_id
  LEFT JOIN affected_records ar ON al.log_id = ar.log_id
`;

const buildWhere = (search, categoryFilter) => {
  const parts  = [];
  const params = [];

  if (search) {
    parts.push(`(
      COALESCE(st.name, stf.name, adm.name) LIKE ?
      OR al.user_id LIKE ?
      OR a.action_name LIKE ?
      OR a.page_context LIKE ?
    )`);
    const like = `%${search}%`;
    params.push(like, like, like, like);
  }

  if (categoryFilter && CATEGORY_ACTIONS[categoryFilter]) {
    const actions = CATEGORY_ACTIONS[categoryFilter];
    parts.push(`a.action_name IN (${actions.map(() => '?').join(',')})`);
    params.push(...actions);
  }

  return { where: parts.length ? `WHERE ${parts.join(' AND ')}` : '', params };
};

const showActivityLog = (req, res) => {
  if (!req.session || !req.session.userId) {
    return res.redirect('/login');
  }
  const user           = { id: req.session.userId, name: req.session.userName };
  const page           = Math.max(1, parseInt(req.query.page) || 1);
  const offset         = (page - 1) * PAGE_SIZE;
  const search         = (req.query.search  || '').trim();
  const categoryFilter = (req.query.category || '').trim();

  const { where, params } = buildWhere(search, categoryFilter);

  try {
    const totalRows = db.prepare(`
      SELECT COUNT(DISTINCT al.log_id) as count
      ${BASE_FROM}
      ${where}
    `).get(...params).count;

    const rows = db.prepare(`
      SELECT
        al.log_id,
        al.user_id,
        al.created_at,
        a.action_name,
        a.page_context,
        a.description,
        COALESCE(st.name, stf.name, adm.name) AS actor_name,
        CASE
          WHEN st.student_number IS NOT NULL THEN 'Student'
          WHEN stf.staff_number  IS NOT NULL THEN 'Lecturer'
          WHEN adm.admin_id      IS NOT NULL THEN 'Admin'
          ELSE 'Unknown'
        END AS actor_role,
        GROUP_CONCAT(ar.table_affected || ':' || ar.record_id, ' | ') AS affected_summary
      ${BASE_FROM}
      ${where}
      GROUP BY al.log_id
      ORDER BY al.created_at DESC
      LIMIT ? OFFSET ?
    `).all(...params, PAGE_SIZE, offset);

    const enriched = rows.map(row => {
      const base = {
        ...row,
        eventLabel: getEventLabel(row.action_name),
        category:   getCategory(row.action_name),
        status:     getStatus(row.action_name),
        actorName:  row.actor_name || resolveActorFallback(row.user_id, row.actor_role),
        old_data:   null,
        new_data:   null,
      };
      if (ADMIN_CRUD_ACTIONS.has(row.action_name)) {
        const audit = fetchAuditData(row.user_id, row.created_at);
        if (audit) { base.old_data = audit.old_data; base.new_data = audit.new_data; }
      }
      return base;
    });

    res.render('admin-activity-log', {
      user,
      rows: enriched,
      page,
      pageSize: PAGE_SIZE,
      totalPages: Math.max(1, Math.ceil(totalRows / PAGE_SIZE)),
      totalRows,
      search,
      categoryFilter,
      categories: Object.keys(CATEGORY_ACTIONS),
      failedLoginCount: getFailedLoginCount(),
      pageTitle: 'Activity Log',
      pageSubtitle: 'System-wide record of all user actions across students, lecturers, and administrators.',
      formAction: '/admin/activity-log',
      showCategoryFilter: true,
      error:   req.query.error   || null,
      success: req.query.success || null,
    });
  } catch (err) {
    console.error('showActivityLog error:', err);
    res.render('admin-activity-log', {
      user,
      rows: [],
      page: 1,
      pageSize: PAGE_SIZE,
      totalPages: 1,
      totalRows: 0,
      search,
      categoryFilter,
      categories: Object.keys(CATEGORY_ACTIONS),
      failedLoginCount: getFailedLoginCount(),
      pageTitle: 'Activity Log',
      pageSubtitle: 'System-wide record of all user actions across students, lecturers, and administrators.',
      formAction: '/admin/activity-log',
      showCategoryFilter: true,
      error:   'Could not load activity log. Please try again.',
      success: null,
    });
  }
};

const showFailedLogins = (req, res) => {
  const user   = { id: req.session.userId, name: req.session.userName };
  const page   = Math.max(1, parseInt(req.query.page) || 1);
  const offset = (page - 1) * PAGE_SIZE;
  const search = (req.query.search || '').trim();

  const parts  = ["a.action_name = 'AUTH_FAILED_LOGIN'"];
  const params = [];
  if (search) {
    parts.push(`(COALESCE(st.name, stf.name, adm.name) LIKE ? OR al.user_id LIKE ?)`);
    const like = `%${search}%`;
    params.push(like, like);
  }
  const where = `WHERE ${parts.join(' AND ')}`;

  try {
    const totalRows = db.prepare(`
      SELECT COUNT(DISTINCT al.log_id) as count ${BASE_FROM} ${where}
    `).get(...params).count;

    const rows = db.prepare(`
      SELECT
        al.log_id, al.user_id, al.created_at,
        a.action_name, a.page_context, a.description,
        COALESCE(st.name, stf.name, adm.name) AS actor_name,
        CASE
          WHEN st.student_number IS NOT NULL THEN 'Student'
          WHEN stf.staff_number  IS NOT NULL THEN 'Lecturer'
          WHEN adm.admin_id      IS NOT NULL THEN 'Admin'
          ELSE 'Unknown'
        END AS actor_role,
        GROUP_CONCAT(ar.table_affected || ':' || ar.record_id, ' | ') AS affected_summary
      ${BASE_FROM} ${where}
      GROUP BY al.log_id
      ORDER BY al.created_at DESC
      LIMIT ? OFFSET ?
    `).all(...params, PAGE_SIZE, offset);

    const enriched = rows.map(row => ({
      ...row,
      eventLabel: getEventLabel(row.action_name),
      category:   getCategory(row.action_name),
      status:     getStatus(row.action_name),
      actorName:  row.actor_name || resolveActorFallback(row.user_id, row.actor_role),
      old_data:   null,
      new_data:   null,
    }));

    res.render('admin-activity-log', {
      user,
      rows: enriched,
      page,
      pageSize: PAGE_SIZE,
      totalPages: Math.max(1, Math.ceil(totalRows / PAGE_SIZE)),
      totalRows,
      search,
      categoryFilter: '',
      categories: [],
      failedLoginCount: totalRows,
      pageTitle: 'Failed Logins',
      pageSubtitle: 'All failed login attempts recorded in the system.',
      formAction: '/admin/failed-logins',
      showCategoryFilter: false,
      error:   req.query.error || null,
      success: null,
    });
  } catch (err) {
    console.error('showFailedLogins error:', err);
    res.render('admin-activity-log', {
      user,
      rows: [],
      page: 1,
      pageSize: PAGE_SIZE,
      totalPages: 1,
      totalRows: 0,
      search,
      categoryFilter: '',
      categories: [],
      failedLoginCount: getFailedLoginCount(),
      pageTitle: 'Failed Logins',
      pageSubtitle: 'All failed login attempts recorded in the system.',
      formAction: '/admin/failed-logins',
      showCategoryFilter: false,
      error:   'Could not load failed logins. Please try again.',
      success: null,
    });
  }
};

module.exports = { showActivityLog, showFailedLogins, getFailedLoginCount };

1	const db = require('../../database/db');	14✔
2	const { getEventLabel, getCategory, getStatus, resolveActorFallback, CATEGORY_ACTIONS } = require('../services/activity-log-helpers');	14✔
3
4	const PAGE_SIZE = 20;	14✔
5	const ADMIN_CRUD_ACTIONS = new Set(['ADMIN_USER_ADD', 'ADMIN_USER_EDIT', 'ADMIN_USER_DELETE']);	14✔
6
7	const getFailedLoginCount = () =>	14✔
8	db.prepare(`	21✔
9	SELECT COUNT(*) as n FROM activity_log al
10	JOIN actions a ON al.action_id = a.action_id
11	WHERE a.action_name = 'AUTH_FAILED_LOGIN'
12	`).get().n;
13
14	const fetchAuditData = (userId, createdAt) =>	14✔
15	db.prepare(`	2✔
16	SELECT old_data, new_data FROM admin_audit_log
17	WHERE admin_id = ?
18	ORDER BY ABS(strftime('%s', timestamp) - strftime('%s', ?))
19	LIMIT 1
20	`).get(userId, createdAt);
21
22	const BASE_FROM = `	14✔
23	FROM activity_log al
24	JOIN actions a ON al.action_id = a.action_id
25	LEFT JOIN students st ON al.user_id = CAST(st.student_number AS TEXT)
26	LEFT JOIN staff stf ON al.user_id = stf.staff_number
27	LEFT JOIN admins adm ON al.user_id = adm.admin_id
28	LEFT JOIN affected_records ar ON al.log_id = ar.log_id
29	`;
30
31	const buildWhere = (search, categoryFilter) => {	14✔
32	const parts = [];	14✔
33	const params = [];	14✔
34
35	if (search) {	14✔
36	parts.push(`(	3✔
37	COALESCE(st.name, stf.name, adm.name) LIKE ?
38	OR al.user_id LIKE ?
39	OR a.action_name LIKE ?
40	OR a.page_context LIKE ?
41	)`);
42	const like = `%${search}%`;	3✔
43	params.push(like, like, like, like);	3✔
44	}
45
46	if (categoryFilter && CATEGORY_ACTIONS[categoryFilter]) {	14✔
47	const actions = CATEGORY_ACTIONS[categoryFilter];	2✔
48	parts.push(`a.action_name IN (${actions.map(() => '?').join(',')})`);	8✔
49	params.push(...actions);	2✔
50	}
51
52	return { where: parts.length ? `WHERE ${parts.join(' AND ')}` : '', params };	14✔
53	};
54
55	const showActivityLog = (req, res) => {	14✔
56	if (!req.session \|\| !req.session.userId) {	14!
NEW 57	return res.redirect('/login');	×
58	}
59	const user = { id: req.session.userId, name: req.session.userName };	14✔
60	const page = Math.max(1, parseInt(req.query.page) \|\| 1);	14✔
61	const offset = (page - 1) * PAGE_SIZE;	14✔
62	const search = (req.query.search \|\| '').trim();	14✔
63	const categoryFilter = (req.query.category \|\| '').trim();	14✔
64
65	const { where, params } = buildWhere(search, categoryFilter);	14✔
66
67	try {	14✔
68	const totalRows = db.prepare(`	14✔
69	SELECT COUNT(DISTINCT al.log_id) as count
70	${BASE_FROM}
71	${where}
72	`).get(...params).count;
73
74	const rows = db.prepare(`	13✔
75	SELECT
76	al.log_id,
77	al.user_id,
78	al.created_at,
79	a.action_name,
80	a.page_context,
81	a.description,
82	COALESCE(st.name, stf.name, adm.name) AS actor_name,
83	CASE
84	WHEN st.student_number IS NOT NULL THEN 'Student'
85	WHEN stf.staff_number IS NOT NULL THEN 'Lecturer'
86	WHEN adm.admin_id IS NOT NULL THEN 'Admin'
87	ELSE 'Unknown'
88	END AS actor_role,
89	GROUP_CONCAT(ar.table_affected \|\| ':' \|\| ar.record_id, ' \| ') AS affected_summary
90	${BASE_FROM}
91	${where}
92	GROUP BY al.log_id
93	ORDER BY al.created_at DESC
94	LIMIT ? OFFSET ?
95	`).all(...params, PAGE_SIZE, offset);
96
97	const enriched = rows.map(row => {	13✔
98	const base = {	38✔
99	...row,
100	eventLabel: getEventLabel(row.action_name),
101	category: getCategory(row.action_name),
102	status: getStatus(row.action_name),
103	actorName: row.actor_name \|\| resolveActorFallback(row.user_id, row.actor_role),	38!
104	old_data: null,
105	new_data: null,
106	};
107	if (ADMIN_CRUD_ACTIONS.has(row.action_name)) {	38✔
108	const audit = fetchAuditData(row.user_id, row.created_at);	2✔
109	if (audit) { base.old_data = audit.old_data; base.new_data = audit.new_data; }	2✔
110	}
111	return base;	38✔
112	});
113
114	res.render('admin-activity-log', {	13✔
115	user,
116	rows: enriched,
117	page,
118	pageSize: PAGE_SIZE,
119	totalPages: Math.max(1, Math.ceil(totalRows / PAGE_SIZE)),
120	totalRows,
121	search,
122	categoryFilter,
123	categories: Object.keys(CATEGORY_ACTIONS),
124	failedLoginCount: getFailedLoginCount(),
125	pageTitle: 'Activity Log',
126	pageSubtitle: 'System-wide record of all user actions across students, lecturers, and administrators.',
127	formAction: '/admin/activity-log',
128	showCategoryFilter: true,
129	error: req.query.error \|\| null,	26✔
130	success: req.query.success \|\| null,	26✔
131	});
132	} catch (err) {
133	console.error('showActivityLog error:', err);	1✔
134	res.render('admin-activity-log', {	1✔
135	user,
136	rows: [],
137	page: 1,
138	pageSize: PAGE_SIZE,
139	totalPages: 1,
140	totalRows: 0,
141	search,
142	categoryFilter,
143	categories: Object.keys(CATEGORY_ACTIONS),
144	failedLoginCount: getFailedLoginCount(),
145	pageTitle: 'Activity Log',
146	pageSubtitle: 'System-wide record of all user actions across students, lecturers, and administrators.',
147	formAction: '/admin/activity-log',
148	showCategoryFilter: true,
149	error: 'Could not load activity log. Please try again.',
150	success: null,
151	});
152	}
153	};
154
155	const showFailedLogins = (req, res) => {	14✔
156	const user = { id: req.session.userId, name: req.session.userName };	5✔
157	const page = Math.max(1, parseInt(req.query.page) \|\| 1);	5✔
158	const offset = (page - 1) * PAGE_SIZE;	5✔
159	const search = (req.query.search \|\| '').trim();	5✔
160
161	const parts = ["a.action_name = 'AUTH_FAILED_LOGIN'"];	5✔
162	const params = [];	5✔
163	if (search) {	5✔
164	parts.push(`(COALESCE(st.name, stf.name, adm.name) LIKE ? OR al.user_id LIKE ?)`);	1✔
165	const like = `%${search}%`;	1✔
166	params.push(like, like);	1✔
167	}
168	const where = `WHERE ${parts.join(' AND ')}`;	5✔
169
170	try {	5✔
171	const totalRows = db.prepare(`	5✔
172	SELECT COUNT(DISTINCT al.log_id) as count ${BASE_FROM} ${where}
173	`).get(...params).count;
174
175	const rows = db.prepare(`	4✔
176	SELECT
177	al.log_id, al.user_id, al.created_at,
178	a.action_name, a.page_context, a.description,
179	COALESCE(st.name, stf.name, adm.name) AS actor_name,
180	CASE
181	WHEN st.student_number IS NOT NULL THEN 'Student'
182	WHEN stf.staff_number IS NOT NULL THEN 'Lecturer'
183	WHEN adm.admin_id IS NOT NULL THEN 'Admin'
184	ELSE 'Unknown'
185	END AS actor_role,
186	GROUP_CONCAT(ar.table_affected \|\| ':' \|\| ar.record_id, ' \| ') AS affected_summary
187	${BASE_FROM} ${where}
188	GROUP BY al.log_id
189	ORDER BY al.created_at DESC
190	LIMIT ? OFFSET ?
191	`).all(...params, PAGE_SIZE, offset);
192
193	const enriched = rows.map(row => ({	4✔
194	...row,
195	eventLabel: getEventLabel(row.action_name),
196	category: getCategory(row.action_name),
197	status: getStatus(row.action_name),
198	actorName: row.actor_name \|\| resolveActorFallback(row.user_id, row.actor_role),	3!
199	old_data: null,
200	new_data: null,
201	}));
202
203	res.render('admin-activity-log', {	4✔
204	user,
205	rows: enriched,
206	page,
207	pageSize: PAGE_SIZE,
208	totalPages: Math.max(1, Math.ceil(totalRows / PAGE_SIZE)),
209	totalRows,
210	search,
211	categoryFilter: '',
212	categories: [],
213	failedLoginCount: totalRows,
214	pageTitle: 'Failed Logins',
215	pageSubtitle: 'All failed login attempts recorded in the system.',
216	formAction: '/admin/failed-logins',
217	showCategoryFilter: false,
218	error: req.query.error \|\| null,	8✔
219	success: null,
220	});
221	} catch (err) {
222	console.error('showFailedLogins error:', err);	1✔
223	res.render('admin-activity-log', {	1✔
224	user,
225	rows: [],
226	page: 1,
227	pageSize: PAGE_SIZE,
228	totalPages: 1,
229	totalRows: 0,
230	search,
231	categoryFilter: '',
232	categories: [],
233	failedLoginCount: getFailedLoginCount(),
234	pageTitle: 'Failed Logins',
235	pageSubtitle: 'All failed login attempts recorded in the system.',
236	formAction: '/admin/failed-logins',
237	showCategoryFilter: false,
238	error: 'Could not load failed logins. Please try again.',
239	success: null,
240	});
241	}
242	};
243
244	module.exports = { showActivityLog, showFailedLogins, getFailedLoginCount };	14✔

witseie-elen4010 / 2026-group-lab-002 / 25973956313

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