Hi ${firstname},
You've been invited to join ${organizationName} on AptivaAI!
AptivaAI is a career exploration platform that helps you discover potential career paths, explore educational programs, and plan your professional future.
Already have an AptivaAI account with a different email?
This invitation link is valid for 7 days.
Questions? Reply to this email or contact your administrator.
— The AptivaAI Team
Hi ${firstname},
You've been invited to join ${organizationName} on AptivaAI!
We noticed you already have an AptivaAI account
You have two options:
Option 1: Link Your Existing Account
Option 2: Create a Separate Account
This invitation link is valid for 7 days.
Questions? Reply to this email or contact your administrator.
— The AptivaAI Team
${emailText}`
});
console.log(`[reset-password] Sent reset email to ${emailNorm}`);
} else {
console.log(`[DEV] Password reset link for ${emailNorm}: ${resetLink}`);
}
return res.json({ message: 'Password reset email sent' });
} catch (err) {
console.error('[reset-password] Error:', err.message);
return res.status(500).json({ error: 'Failed to send password reset' });
}
});
// Deactivate student account
app.post('/api/admin/students/:studentId/deactivate', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const { studentId } = req.params;
const { status } = req.body;
// Validate status
const validStatuses = ['graduated', 'withdrawn', 'transferred', 'inactive'];
const newStatus = validStatuses.includes(status) ? status : 'inactive';
// Update student enrollment status (studentId is organization_students.id)
const [result] = await pool.execute(`
UPDATE organization_students
SET enrollment_status = ?,
status_changed_date = NOW()
WHERE organization_id = ? AND id = ?
`, [newStatus, orgId, studentId]);
if (result.affectedRows === 0) {
return res.status(404).json({ error: 'Student not found' });
}
console.log(`[deactivate-student] Student ${studentId} marked as ${newStatus}`);
return res.json({ message: `Student marked as ${newStatus}` });
} catch (err) {
console.error('[deactivate-student] Error:', err.message);
return res.status(500).json({ error: 'Failed to update student status' });
}
});
// Resend invitation to single student
app.post('/api/admin/students/:studentId/resend-invitation', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const { studentId } = req.params;
// Get student info (studentId is organization_students.id)
const [students] = await pool.execute(`
SELECT os.*, up.email, up.firstname, up.lastname
FROM organization_students os
JOIN user_profile up ON os.user_id = up.id
WHERE os.organization_id = ? AND os.id = ?
LIMIT 1
`, [orgId, studentId]);
if (!students || students.length === 0) {
return res.status(404).json({ error: 'Student not found' });
}
const student = students[0];
const userId = student.user_id;
// Decrypt fields
let email = student.email;
let firstname = student.firstname;
try {
email = decrypt(email);
firstname = decrypt(firstname);
} catch (err) {
// Fields might not be encrypted
}
// Update invitation_sent_at
await pool.execute(`
UPDATE organization_students
SET invitation_sent_at = NOW()
WHERE organization_id = ? AND id = ?
`, [orgId, studentId]);
// Send invitation email
sendStudentInvitation(email, firstname, req.admin.organizationName, orgId, userId).catch(emailErr => {
console.error(`[resend-invitation] Email send failed for ${email}:`, emailErr.message);
});
return res.json({ message: 'Invitation resent' });
} catch (err) {
console.error('[resend-invitation] Error:', err.message);
return res.status(500).json({ error: 'Failed to resend invitation' });
}
});
// Resend all pending invitations
app.post('/api/admin/students/resend-all-invitations', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
// Get all pending invitation students
const [students] = await pool.execute(`
SELECT os.user_id, up.email, up.firstname
FROM organization_students os
JOIN user_profile up ON os.user_id = up.id
WHERE os.organization_id = ? AND os.enrollment_status = 'pending_invitation'
`, [orgId]);
if (!students || students.length === 0) {
return res.json({ message: 'No pending invitations to resend' });
}
// Update all invitation_sent_at
await pool.execute(`
UPDATE organization_students
SET invitation_sent_at = NOW()
WHERE organization_id = ? AND enrollment_status = 'pending_invitation'
`, [orgId]);
// Send emails to all
for (const student of students) {
let email = student.email;
let firstname = student.firstname;
const userId = student.user_id;
try {
email = decrypt(email);
firstname = decrypt(firstname);
} catch (err) {
// Not encrypted
}
sendStudentInvitation(email, firstname, req.admin.organizationName, orgId, userId).catch(emailErr => {
console.error(`[resend-all] Email send failed for ${email}:`, emailErr.message);
});
}
return res.json({ message: `Invitations resent to ${students.length} students` });
} catch (err) {
console.error('[resend-all] Error:', err.message);
return res.status(500).json({ error: 'Failed to resend invitations' });
}
});
// Update email for bounced invitation
app.post('/api/admin/students/:studentId/update-email', requireAdminAuth, async (req, res) => {
const { email } = req.body;
if (!email || !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
return res.status(400).json({ error: 'Valid email required' });
}
try {
const orgId = req.admin.organizationId;
const { studentId } = req.params;
// Get student info (studentId is organization_students.id)
const [students] = await pool.execute(`
SELECT os.*, up.firstname
FROM organization_students os
JOIN user_profile up ON os.user_id = up.id
WHERE os.organization_id = ? AND os.id = ?
LIMIT 1
`, [orgId, studentId]);
if (!students || students.length === 0) {
return res.status(404).json({ error: 'Student not found' });
}
const student = students[0];
const userId = student.user_id; // Extract for user_profile update
// Decrypt firstname
let firstname = student.firstname;
try {
firstname = decrypt(firstname);
} catch (err) {
// Not encrypted
}
// Encrypt new email
const emailNorm = email.toLowerCase().trim();
const encEmail = encrypt(emailNorm);
const emailLookup = generateHMAC(emailNorm);
// Update email in user_profile
await pool.execute(`
UPDATE user_profile
SET email = ?, email_lookup = ?
WHERE id = ?
`, [encEmail, emailLookup, userId]);
// Update organization_students status back to pending_invitation
await pool.execute(`
UPDATE organization_students
SET enrollment_status = 'pending_invitation',
invitation_sent_at = NOW(),
bounce_reason = NULL
WHERE organization_id = ? AND id = ?
`, [orgId, studentId]);
// Send invitation email
sendStudentInvitation(emailNorm, firstname, req.admin.organizationName, orgId, userId).catch(emailErr => {
console.error(`[update-email] Email send failed for ${emailNorm}:`, emailErr.message);
});
return res.json({ message: 'Email updated and invitation resent' });
} catch (err) {
console.error('[update-email] Error:', err.message);
return res.status(500).json({ error: 'Failed to update email' });
}
});
app.post('/api/admin/students', requireAdminAuth, async (req, res) => {
const { email, firstname, lastname, grade_level } = req.body;
if (!email || !firstname || !lastname) {
return res.status(400).json({ error: 'Email, firstname, and lastname required' });
}
// Basic email validation
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
return res.status(400).json({ error: 'Invalid email format' });
}
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const orgId = req.admin.organizationId;
// Get organization state, area, type, and onboarding delay setting
const [orgs] = await conn.execute(
'SELECT state, city, organization_type, onboarding_delay_days FROM organizations WHERE id = ? LIMIT 1',
[orgId]
);
const orgState = orgs[0]?.state || '';
const orgArea = orgs[0]?.city || '';
const orgType = orgs[0]?.organization_type || '';
const onboardingDelayDays = orgs[0]?.onboarding_delay_days || 14; // Default 14 days
// Email lookup (HMAC)
const EMAIL_INDEX_KEY = process.env.EMAIL_INDEX_SECRET || JWT_SECRET;
const emailLookup = crypto
.createHmac('sha256', EMAIL_INDEX_KEY)
.update(String(email).trim().toLowerCase())
.digest('hex');
const [existingUsers] = await conn.execute(
'SELECT id FROM user_profile WHERE email_lookup = ? LIMIT 1',
[emailLookup]
);
let userId;
if (existingUsers.length > 0) {
userId = existingUsers[0].id;
const [existingEnrollment] = await conn.execute(
'SELECT id FROM organization_students WHERE organization_id = ? AND user_id = ?',
[orgId, userId]
);
if (existingEnrollment.length > 0) {
await conn.rollback();
return res.status(409).json({ error: 'Student already enrolled in organization' });
}
} else {
// Encrypt email
const emailNorm = String(email).trim().toLowerCase();
const encEmail = encrypt(emailNorm);
const [userResult] = await conn.execute(
'INSERT INTO user_profile (email, email_lookup, firstname, lastname, state, area, is_premium, created_at) VALUES (?, ?, ?, ?, ?, ?, 1, NOW())',
[encEmail, emailLookup, firstname, lastname, orgState, orgArea]
);
userId = userResult.insertId;
}
// Calculate onboarding trigger date
// K-12 Schools: delay for grades 11-12 only
// Other institutions: trigger immediately for all students
let triggerDate = null;
if (orgType === 'K-12 School') {
const shouldTriggerOnboarding = grade_level >= 11 && grade_level <= 12;
triggerDate = shouldTriggerOnboarding ? new Date(Date.now() + onboardingDelayDays * 24 * 60 * 60 * 1000) : null;
} else {
// Non-K12: trigger immediately
triggerDate = new Date();
}
await conn.execute(
'INSERT INTO organization_students (organization_id, user_id, enrollment_status, enrollment_date, grade_level, onboarding_triggered_at) VALUES (?, ?, ?, NOW(), ?, ?)',
[orgId, userId, 'active', grade_level || null, triggerDate]
);
// Do NOT insert privacy settings - let modal prompt user to configure on first login
await conn.commit();
// Send invitation email (async, don't block response)
try {
await sendStudentInvitation(email, firstname, req.admin.organizationName, orgId, userId);
} catch (emailErr) {
console.error('[add-student] Email send failed:', emailErr.message);
// Don't fail the request if email fails
}
return res.status(201).json({
message: 'Student added successfully',
userId,
email
});
} catch (err) {
await conn.rollback();
console.error('[add-student] Error:', err.message);
if (err.stack) console.error(err.stack);
return res.status(500).json({ error: 'Failed to add student' });
} finally {
conn.release();
}
});
app.post('/api/admin/students/bulk', requireAdminAuth, bulkUploadLimiter, async (req, res) => {
const { students } = req.body;
if (!Array.isArray(students) || students.length === 0) {
return res.status(400).json({ error: 'Students array required' });
}
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const orgId = req.admin.organizationId;
const results = {
added: 0,
updated: 0,
skipped: 0,
errors: []
};
// Get organization state, area, type, and onboarding delay setting
const [orgs] = await conn.execute(
'SELECT state, city, organization_type, onboarding_delay_days FROM organizations WHERE id = ? LIMIT 1',
[orgId]
);
const orgState = orgs[0]?.state || '';
const orgArea = orgs[0]?.city || '';
const orgType = orgs[0]?.organization_type || '';
const onboardingDelayDays = orgs[0]?.onboarding_delay_days || 14; // Default 14 days
const EMAIL_INDEX_KEY = process.env.EMAIL_INDEX_SECRET || JWT_SECRET;
for (const student of students) {
const { email, firstname, lastname, grade_level } = student;
if (!email || !firstname || !lastname) {
results.errors.push({ email, error: 'Missing required fields' });
continue;
}
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
results.errors.push({ email, error: 'Invalid email format' });
continue;
}
try {
const emailNorm = String(email).trim().toLowerCase();
const emailLookup = crypto
.createHmac('sha256', EMAIL_INDEX_KEY)
.update(emailNorm)
.digest('hex');
const [existingUsers] = await conn.execute(
'SELECT id FROM user_profile WHERE email_lookup = ? LIMIT 1',
[emailLookup]
);
let userId;
if (existingUsers.length > 0) {
userId = existingUsers[0].id;
const [existingEnrollment] = await conn.execute(
'SELECT id FROM organization_students WHERE organization_id = ? AND user_id = ?',
[orgId, userId]
);
if (existingEnrollment.length > 0) {
results.skipped++;
continue;
}
} else {
const encEmail = encrypt(emailNorm);
const encFirstname = encrypt(firstname);
const encLastname = encrypt(lastname);
const [userResult] = await conn.execute(
'INSERT INTO user_profile (email, email_lookup, firstname, lastname, state, area, is_premium, created_at) VALUES (?, ?, ?, ?, ?, ?, 1, NOW())',
[encEmail, emailLookup, encFirstname, encLastname, orgState, orgArea]
);
userId = userResult.insertId;
}
// Calculate onboarding trigger date
// K-12 Schools: delay for grades 11-12 only
// Other institutions: trigger immediately for all students
let triggerDate = null;
if (orgType === 'K-12 School') {
const shouldTriggerOnboarding = grade_level >= 11 && grade_level <= 12;
triggerDate = shouldTriggerOnboarding ? new Date(Date.now() + onboardingDelayDays * 24 * 60 * 60 * 1000) : null;
} else {
// Non-K12: trigger immediately
triggerDate = new Date();
}
await conn.execute(
'INSERT INTO organization_students (organization_id, user_id, enrollment_status, enrollment_date, grade_level, onboarding_triggered_at) VALUES (?, ?, ?, NOW(), ?, ?)',
[orgId, userId, 'active', grade_level || null, triggerDate]
);
// Do NOT insert privacy settings - let modal prompt user to configure on first login
results.added++;
// Send invitation email (async, don't block loop)
sendStudentInvitation(email, firstname, req.admin.organizationName, orgId, userId).catch(emailErr => {
console.error(`[bulk-upload] Email send failed for ${email}:`, emailErr.message);
});
} catch (err) {
results.errors.push({ email, error: err.message });
}
}
await conn.commit();
return res.json({
message: 'Bulk upload completed',
results
});
} catch (err) {
await conn.rollback();
console.error('[bulk-upload] Error:', err.message);
if (err.stack) console.error(err.stack);
return res.status(500).json({ error: 'Bulk upload failed' });
} finally {
conn.release();
}
});
app.patch('/api/admin/students/:studentId/status', requireAdminAuth, async (req, res) => {
const { studentId } = req.params;
const { status } = req.body;
const validStatuses = ['active', 'graduated', 'withdrawn', 'transferred'];
if (!validStatuses.includes(status)) {
return res.status(400).json({ error: 'Invalid status. Must be: active, graduated, withdrawn, transferred' });
}
try {
const orgId = req.admin.organizationId;
// studentId is organization_students.id
const [existing] = await pool.execute(
'SELECT id FROM organization_students WHERE organization_id = ? AND id = ?',
[orgId, studentId]
);
if (existing.length === 0) {
return res.status(404).json({ error: 'Student not found in organization' });
}
await pool.execute(
'UPDATE organization_students SET enrollment_status = ?, updated_at = NOW() WHERE organization_id = ? AND id = ?',
[status, orgId, studentId]
);
return res.json({ message: 'Student status updated', status });
} catch (err) {
console.error('[update-status] Error:', err.message);
return res.status(500).json({ error: 'Failed to update student status' });
}
});
app.delete('/api/admin/students/:studentId', requireAdminAuth, requireSuperAdmin, async (req, res) => {
const { studentId } = req.params;
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const orgId = req.admin.organizationId;
// studentId is organization_students.id - get user_id for related deletes
const [existing] = await conn.execute(
'SELECT id, user_id FROM organization_students WHERE organization_id = ? AND id = ?',
[orgId, studentId]
);
if (existing.length === 0) {
await conn.rollback();
return res.status(404).json({ error: 'Student not found in organization' });
}
const userId = existing[0].user_id;
await conn.execute(
'DELETE FROM organization_students WHERE organization_id = ? AND id = ?',
[orgId, studentId]
);
await conn.execute(
'DELETE FROM student_privacy_settings WHERE organization_id = ? AND user_id = ?',
[orgId, userId]
);
await conn.execute(
'DELETE FROM user_emails WHERE organization_id = ? AND user_id = ?',
[orgId, userId]
);
await conn.commit();
return res.json({ message: 'Student removed from organization' });
} catch (err) {
await conn.rollback();
console.error('[delete-student] Error:', err.message);
if (err.stack) console.error(err.stack);
return res.status(500).json({ error: 'Failed to remove student' });
} finally {
conn.release();
}
});
app.get('/api/admin/students/pending', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [pending] = await pool.execute(`
SELECT ue.email, ue.email_status, ue.sent_at, ue.bounced_at,
up.firstname, up.lastname, up.created_at
FROM user_emails ue
JOIN user_profile up ON ue.user_id = up.id
WHERE ue.organization_id = ? AND ue.email_status IN ('pending', 'bounced')
ORDER BY ue.created_at DESC
`, [orgId]);
return res.json({ pending });
} catch (err) {
console.error('[pending-invitations] Error:', err.message);
return res.status(500).json({ error: 'Failed to load pending invitations' });
}
});
app.post('/api/admin/students/resend-invitation', requireAdminAuth, resendLimiter, async (req, res) => {
const { userId } = req.body;
if (!userId) {
return res.status(400).json({ error: 'userId required' });
}
try {
const orgId = req.admin.organizationId;
const [result] = await pool.execute(
'UPDATE user_emails SET email_status = ?, sent_at = NOW() WHERE organization_id = ? AND user_id = ?',
['pending', orgId, userId]
);
if (result.affectedRows === 0) {
return res.status(404).json({ error: 'Email record not found' });
}
// TODO: Trigger actual email send via SendGrid/Twilio
return res.json({ message: 'Invitation resent' });
} catch (err) {
console.error('[resend-invitation] Error:', err.message);
return res.status(500).json({ error: 'Failed to resend invitation' });
}
});
// ═══════════════════════════════════════════════════════════════════════════
// ORGANIZATION SETTINGS ENDPOINTS (Super Admin Only)
// ═══════════════════════════════════════════════════════════════════════════
app.get('/api/admin/organization/profile', requireAdminAuth, requireSuperAdmin, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [orgs] = await pool.execute(
'SELECT * FROM organizations WHERE id = ? LIMIT 1',
[orgId]
);
// If organization doesn't exist, return defaults
if (orgs.length === 0) {
return res.json({
organization_name: req.admin.organizationName || '',
organization_type: '',
address: '',
city: '',
state: '',
zip_code: '',
primary_contact_name: '',
primary_contact_email: '',
primary_contact_phone: ''
});
}
return res.json(orgs[0]);
} catch (err) {
console.error('[get-organization] Error:', err.message);
return res.status(500).json({ error: 'Failed to load organization' });
}
});
app.put('/api/admin/organization/profile', requireAdminAuth, requireSuperAdmin, async (req, res) => {
const allowedFields = [
'organization_name', 'organization_type', 'address', 'city', 'state', 'zip_code',
'primary_contact_name', 'primary_contact_email', 'primary_contact_phone',
'onboarding_delay_days'
];
const updates = {};
for (const field of allowedFields) {
if (req.body[field] !== undefined) {
updates[field] = req.body[field];
}
}
if (Object.keys(updates).length === 0) {
return res.status(400).json({ error: 'No valid fields to update' });
}
try {
const orgId = req.admin.organizationId;
// Check if organization exists
const [existing] = await pool.execute(
'SELECT id FROM organizations WHERE id = ? LIMIT 1',
[orgId]
);
if (existing.length === 0) {
// Create organization if it doesn't exist - ensure required fields have defaults
const defaults = {
organization_name: req.admin.organizationName || 'Unnamed Organization',
organization_type: 'Other',
state: null,
...updates
};
const fields = Object.keys(defaults);
const placeholders = fields.map(() => '?').join(', ');
const values = Object.values(defaults);
await pool.execute(
`INSERT INTO organizations (id, ${fields.join(', ')}, created_at) VALUES (?, ${placeholders}, NOW())`,
[orgId, ...values]
);
} else {
// Update existing organization
const setClause = Object.keys(updates).map(k => `${k} = ?`).join(', ');
const values = [...Object.values(updates), orgId];
await pool.execute(
`UPDATE organizations SET ${setClause}, updated_at = NOW() WHERE id = ?`,
values
);
}
return res.json({ message: 'Organization profile updated' });
} catch (err) {
console.error('[update-organization] Error:', err.message);
return res.status(500).json({ error: 'Failed to update organization' });
}
});
app.get('/api/admin/organization/calendar', requireAdminAuth, requireSuperAdmin, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [calendars] = await pool.execute(
'SELECT * FROM academic_calendars WHERE organization_id = ? LIMIT 1',
[orgId]
);
// Return defaults if no calendar exists
if (calendars.length === 0) {
return res.json({
fall_roster_window_start: '',
fall_roster_window_end: '',
spring_roster_window_start: '',
spring_roster_window_end: '',
roster_change_threshold: 30
});
}
return res.json(calendars[0]);
} catch (err) {
console.error('[get-calendar] Error:', err.message);
return res.status(500).json({ error: 'Failed to load calendar' });
}
});
app.put('/api/admin/organization/calendar', requireAdminAuth, requireSuperAdmin, async (req, res) => {
const {
fall_roster_window_start,
fall_roster_window_end,
spring_roster_window_start,
spring_roster_window_end,
roster_change_threshold
} = req.body;
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const orgId = req.admin.organizationId;
const [existing] = await conn.execute(
'SELECT id FROM academic_calendars WHERE organization_id = ? LIMIT 1',
[orgId]
);
if (existing.length === 0) {
// Create new calendar
await conn.execute(
`INSERT INTO academic_calendars (
organization_id,
fall_roster_window_start,
fall_roster_window_end,
spring_roster_window_start,
spring_roster_window_end,
roster_change_threshold,
created_at
) VALUES (?, ?, ?, ?, ?, ?, NOW())`,
[
orgId,
fall_roster_window_start || null,
fall_roster_window_end || null,
spring_roster_window_start || null,
spring_roster_window_end || null,
roster_change_threshold || 30
]
);
} else {
// Update existing calendar
await conn.execute(
`UPDATE academic_calendars SET
fall_roster_window_start = ?,
fall_roster_window_end = ?,
spring_roster_window_start = ?,
spring_roster_window_end = ?,
roster_change_threshold = ?,
updated_at = NOW()
WHERE organization_id = ?`,
[
fall_roster_window_start || null,
fall_roster_window_end || null,
spring_roster_window_start || null,
spring_roster_window_end || null,
roster_change_threshold || 30,
orgId
]
);
}
await conn.commit();
return res.json({ message: 'Calendar updated' });
} catch (err) {
await conn.rollback();
console.error('[update-calendar] Error:', err.message);
if (err.stack) console.error(err.stack);
return res.status(500).json({ error: 'Failed to update calendar' });
} finally {
conn.release();
}
});
app.get('/api/admin/organization/admins', requireAdminAuth, requireSuperAdmin, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [admins] = await pool.execute(`
SELECT oa.id, oa.user_id, oa.role, oa.created_at,
up.firstname, up.lastname, up.email, up.last_login
FROM organization_admins oa
JOIN user_profile up ON oa.user_id = up.id
WHERE oa.organization_id = ?
ORDER BY oa.created_at DESC
`, [orgId]);
// Decrypt emails
for (const admin of admins) {
if (admin.email) {
try {
admin.email = decrypt(admin.email);
} catch {
// Leave as-is if not encrypted
}
}
// SECURITY: Remove user_id before sending to frontend
delete admin.user_id;
}
return res.json(admins);
} catch (err) {
console.error('[list-admins] Error:', err.message);
if (err.stack) console.error(err.stack);
return res.status(500).json({ error: 'Failed to load admins' });
}
});
app.post('/api/admin/organization/admins', requireAdminAuth, requireSuperAdmin, async (req, res) => {
const { email, firstname, lastname, role = 'staff_admin' } = req.body;
if (!email || !firstname || !lastname) {
return res.status(400).json({ error: 'Email, firstname, and lastname required' });
}
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
return res.status(400).json({ error: 'Invalid email format' });
}
// Schools can only create staff_admin - super_admin is created manually by Aptiva
if (role === 'super_admin') {
return res.status(403).json({ error: 'Cannot create Super Admin users. Contact AptivaAI support for Super Admin access.' });
}
if (!['staff_admin'].includes(role)) {
return res.status(400).json({ error: 'Invalid role. Must be: staff_admin' });
}
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const orgId = req.admin.organizationId;
const EMAIL_INDEX_KEY = process.env.EMAIL_INDEX_SECRET || JWT_SECRET;
const emailNorm = String(email).trim().toLowerCase();
const emailLookup = crypto
.createHmac('sha256', EMAIL_INDEX_KEY)
.update(emailNorm)
.digest('hex');
const [existingUsers] = await conn.execute(
'SELECT id FROM user_profile WHERE email_lookup = ? LIMIT 1',
[emailLookup]
);
let userId;
if (existingUsers.length > 0) {
userId = existingUsers[0].id;
const [existingAdmin] = await conn.execute(
'SELECT id FROM organization_admins WHERE organization_id = ? AND user_id = ?',
[orgId, userId]
);
if (existingAdmin.length > 0) {
await conn.rollback();
return res.status(409).json({ error: 'User is already an admin for this organization' });
}
} else {
const encEmail = encrypt(emailNorm);
const [userResult] = await conn.execute(
'INSERT INTO user_profile (email, email_lookup, firstname, lastname, created_at) VALUES (?, ?, ?, ?, NOW())',
[encEmail, emailLookup, firstname, lastname]
);
userId = userResult.insertId;
}
await conn.execute(
'INSERT INTO organization_admins (organization_id, user_id, role) VALUES (?, ?, ?)',
[orgId, userId, role]
);
await conn.commit();
return res.status(201).json({
message: 'Admin added successfully',
userId,
email,
role
});
} catch (err) {
await conn.rollback();
console.error('[add-admin] Error:', err.message);
if (err.stack) console.error(err.stack);
return res.status(500).json({ error: 'Failed to add admin' });
} finally {
conn.release();
}
});
app.patch('/api/admin/organization/admins/:adminId', requireAdminAuth, requireSuperAdmin, async (req, res) => {
const { adminId } = req.params;
const { role } = req.body;
// Schools can only assign staff_admin role - super_admin is managed by Aptiva
if (role === 'super_admin') {
return res.status(403).json({ error: 'Cannot assign Super Admin role. Contact AptivaAI support for Super Admin access.' });
}
if (!['staff_admin'].includes(role)) {
return res.status(400).json({ error: 'Invalid role. Must be: staff_admin' });
}
try {
const orgId = req.admin.organizationId;
const [currentAdmin] = await pool.execute(
'SELECT user_id FROM organization_admins WHERE id = ? AND organization_id = ?',
[adminId, orgId]
);
if (currentAdmin.length === 0) {
return res.status(404).json({ error: 'Admin not found' });
}
if (currentAdmin[0].user_id === req.admin.userId) {
return res.status(403).json({ error: 'Cannot change your own role' });
}
await pool.execute(
'UPDATE organization_admins SET role = ?, updated_at = NOW() WHERE id = ? AND organization_id = ?',
[role, adminId, orgId]
);
return res.json({ message: 'Admin role updated', role });
} catch (err) {
console.error('[update-admin-role] Error:', err.message);
return res.status(500).json({ error: 'Failed to update admin role' });
}
});
app.delete('/api/admin/organization/admins/:adminId', requireAdminAuth, requireSuperAdmin, async (req, res) => {
const { adminId } = req.params;
try {
const orgId = req.admin.organizationId;
const [admin] = await pool.execute(
'SELECT user_id FROM organization_admins WHERE id = ? AND organization_id = ?',
[adminId, orgId]
);
if (admin.length === 0) {
return res.status(404).json({ error: 'Admin not found' });
}
if (admin[0].user_id === req.admin.userId) {
return res.status(403).json({ error: 'Cannot remove yourself as admin' });
}
const [superAdmins] = await pool.execute(
'SELECT COUNT(*) as count FROM organization_admins WHERE organization_id = ? AND role = ?',
[orgId, 'super_admin']
);
const [removingAdmin] = await pool.execute(
'SELECT role FROM organization_admins WHERE id = ?',
[adminId]
);
if (superAdmins[0].count === 1 && removingAdmin[0].role === 'super_admin') {
return res.status(403).json({ error: 'Cannot remove the last super admin' });
}
await pool.execute(
'DELETE FROM organization_admins WHERE id = ? AND organization_id = ?',
[adminId, orgId]
);
return res.json({ message: 'Admin removed successfully' });
} catch (err) {
console.error('[remove-admin] Error:', err.message);
return res.status(500).json({ error: 'Failed to remove admin' });
}
});
app.get('/api/admin/organization/billing', requireAdminAuth, requireSuperAdmin, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [billing] = await pool.execute(
'SELECT * FROM organization_billing WHERE organization_id = ? ORDER BY created_at DESC',
[orgId]
);
return res.json({ billing });
} catch (err) {
console.error('[get-billing] Error:', err.message);
return res.status(500).json({ error: 'Failed to load billing information' });
}
});
// ═══════════════════════════════════════════════════════════════════════════
// ROSTER UPDATE ENDPOINTS
// ═══════════════════════════════════════════════════════════════════════════
app.post('/api/admin/roster/upload', requireAdminAuth, rosterUploadLimiter, async (req, res) => {
const { students } = req.body;
if (!Array.isArray(students) || students.length === 0) {
return res.status(400).json({ error: 'Students array required' });
}
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const orgId = req.admin.organizationId;
const submittedBy = req.admin.userId;
const [currentCount] = await conn.execute(
'SELECT COUNT(*) as count FROM organization_students WHERE organization_id = ? AND enrollment_status = ?',
[orgId, 'active']
);
const previousCount = currentCount[0].count;
const results = { added: 0, updated: 0, skipped: 0, errors: [] };
// Get organization state, area, type, and onboarding delay setting
const [orgs] = await conn.execute(
'SELECT state, city, organization_type, onboarding_delay_days FROM organizations WHERE id = ? LIMIT 1',
[orgId]
);
const orgState = orgs[0]?.state || '';
const orgArea = orgs[0]?.city || '';
const orgType = orgs[0]?.organization_type || '';
const onboardingDelayDays = orgs[0]?.onboarding_delay_days || 14; // Default 14 days
const EMAIL_INDEX_KEY = process.env.EMAIL_INDEX_SECRET || JWT_SECRET;
for (const student of students) {
const { email, firstname, lastname, status = 'active', grade_level } = student;
if (!email || !firstname || !lastname) {
results.errors.push({ email, error: 'Missing required fields' });
continue;
}
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
results.errors.push({ email, error: 'Invalid email format' });
continue;
}
try {
const emailNorm = String(email).trim().toLowerCase();
const emailLookup = crypto
.createHmac('sha256', EMAIL_INDEX_KEY)
.update(emailNorm)
.digest('hex');
// Check for existing user in BOTH user_profile (primary email) AND user_emails (secondary emails)
let userId = null;
let isNewUser = false;
// First check user_profile for primary email
const [existingUsers] = await conn.execute(
'SELECT id FROM user_profile WHERE email_lookup = ? LIMIT 1',
[emailLookup]
);
if (existingUsers.length > 0) {
userId = existingUsers[0].id;
} else {
// Check user_emails for secondary emails
const [secondaryEmails] = await conn.execute(
'SELECT user_id FROM user_emails WHERE email_lookup = ? LIMIT 1',
[emailLookup]
);
if (secondaryEmails.length > 0) {
userId = secondaryEmails[0].user_id;
}
}
// If user found (primary or secondary email), check if already enrolled in THIS org
if (userId) {
const [existingEnrollment] = await conn.execute(
'SELECT id FROM organization_students WHERE organization_id = ? AND user_id = ?',
[orgId, userId]
);
if (existingEnrollment.length > 0) {
// Already enrolled - just update the record (grade level, status, etc.)
await conn.execute(
'UPDATE organization_students SET enrollment_status = ?, grade_level = ?, updated_at = NOW() WHERE organization_id = ? AND user_id = ?',
[status, grade_level || null, orgId, userId]
);
results.updated++;
// Don't send email for roster updates - skip to next student
continue;
}
// User exists but not enrolled in this org yet - will enroll below and send email
} else {
// Email not found anywhere - create shell user_profile for invitation tracking
const encEmail = encrypt(emailNorm);
const encFirstname = encrypt(firstname);
const encLastname = encrypt(lastname);
const [userResult] = await conn.execute(
'INSERT INTO user_profile (email, email_lookup, firstname, lastname, state, area, is_premium, created_at) VALUES (?, ?, ?, ?, ?, ?, 1, NOW())',
[encEmail, emailLookup, encFirstname, encLastname, orgState, orgArea]
);
userId = userResult.insertId;
isNewUser = true;
}
// Determine enrollment status: new users are pending_invitation, existing users are also pending_invitation until they accept
const enrollmentStatus = 'pending_invitation';
// Calculate onboarding trigger date
// K-12 Schools: delay for grades 11-12 only
// Other institutions: trigger immediately for all students
let triggerDate = null;
if (orgType === 'K-12 School') {
const shouldTriggerOnboarding = grade_level >= 11 && grade_level <= 12;
triggerDate = shouldTriggerOnboarding ? new Date(Date.now() + onboardingDelayDays * 24 * 60 * 60 * 1000) : null;
} else {
// Non-K12: trigger immediately
triggerDate = new Date();
}
await conn.execute(
'INSERT INTO organization_students (organization_id, user_id, enrollment_status, enrollment_date, invitation_sent_at, grade_level, onboarding_triggered_at) VALUES (?, ?, ?, NOW(), ?, ?, ?)',
[orgId, userId, enrollmentStatus, isNewUser ? new Date() : null, grade_level || null, triggerDate]
);
// Do NOT insert privacy settings - let modal prompt user to configure on first login
results.added++;
// Send invitation email to ALL users (new and existing)
sendStudentInvitation(email, firstname, req.admin.organizationName, orgId, userId, isNewUser).catch(emailErr => {
console.error(`[roster-upload] Email send failed for ${email}:`, emailErr.message);
});
} catch (err) {
results.errors.push({ email, error: err.message });
}
}
// Get final count after processing (include active + pending, exclude deactivated)
const [finalCount] = await conn.execute(
'SELECT COUNT(*) as count FROM organization_students WHERE organization_id = ? AND enrollment_status NOT IN (?, ?, ?)',
[orgId, 'withdrawn', 'transferred', 'inactive']
);
const totalRosterSize = finalCount[0].count;
let changePercentage = previousCount > 0 ? ((totalRosterSize - previousCount) / previousCount) * 100 : 100;
// Ensure changePercentage is a valid number for decimal(5,2)
if (!isFinite(changePercentage)) changePercentage = 0;
changePercentage = Math.max(-999.99, Math.min(999.99, changePercentage));
// Insert roster update history record
await conn.execute(
`INSERT INTO roster_updates (
organization_id, admin_user_id, update_type, update_window_start, update_window_end, submitted_at,
students_added, students_graduated, students_withdrawn, students_transferred, students_reactivated,
total_roster_size, change_percentage, discount_eligible, flagged_for_review
) VALUES (?, ?, 'manual_upload', CURDATE(), CURDATE(), NOW(), ?, 0, 0, 0, 0, ?, ?, 0, 0)`,
[orgId, submittedBy, results.added, totalRosterSize, changePercentage]
);
await conn.commit();
return res.json({
message: 'Roster upload completed',
changePercent: changePercentage.toFixed(2),
results
});
} catch (err) {
await conn.rollback();
console.error('[roster-upload] Error:', err.message);
if (err.stack) console.error(err.stack);
return res.status(500).json({ error: `Roster upload failed: ${err.message}` });
} finally {
conn.release();
}
});
app.get('/api/admin/roster/history', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [history] = await pool.execute(`
SELECT
ru.id,
ru.submitted_at AS uploaded_at,
ru.students_added,
ru.students_graduated + ru.students_withdrawn + ru.students_transferred AS students_existing,
ru.total_roster_size AS total_students_after,
ru.change_percentage
FROM roster_updates ru
WHERE ru.organization_id = ?
ORDER BY ru.submitted_at DESC
LIMIT 50
`, [orgId]);
// Return empty array if no history, not an error
return res.json(history || []);
} catch (err) {
console.error('[roster-history] Error:', err.message);
// Return empty array instead of error for better UX
return res.json([]);
}
});
app.get('/api/admin/roster/discount-status', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [latest] = await pool.execute(
'SELECT * FROM roster_updates WHERE organization_id = ? ORDER BY submitted_at DESC LIMIT 1',
[orgId]
);
if (latest.length === 0) {
return res.json({ discountStatus: 'no_submissions', message: 'No roster submissions yet' });
}
return res.json({
discountStatus: latest[0].discount_eligibility_status,
changePercent: latest[0].change_percent,
submittedAt: latest[0].submitted_at
});
} catch (err) {
console.error('[discount-status] Error:', err.message);
return res.status(500).json({ error: 'Failed to load discount status' });
}
});
// ═══════════════════════════════════════════════════════════════════════════
// ANALYTICS & REPORTS ENDPOINTS
// ═══════════════════════════════════════════════════════════════════════════
app.get('/api/admin/reports/engagement', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const days = parseInt(req.query.days) || 30;
const [dailyActive] = await pool.execute(`
SELECT DATE(up.last_login) as date, COUNT(DISTINCT up.id) as active_users
FROM organization_students os
JOIN user_profile up ON os.user_id = up.id
WHERE os.organization_id = ?
AND os.enrollment_status = 'active'
AND up.last_login >= DATE_SUB(NOW(), INTERVAL ? DAY)
GROUP BY DATE(up.last_login)
ORDER BY date DESC
`, [orgId, days]);
const [careerActivity] = await pool.execute(`
SELECT DATE(cv.viewed_at) as date, COUNT(*) as views
FROM career_views cv
JOIN organization_students os ON cv.user_id = os.user_id
WHERE os.organization_id = ?
AND cv.viewed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
GROUP BY DATE(cv.viewed_at)
ORDER BY date DESC
`, [orgId, days]);
return res.json({
dailyActive,
careerActivity
});
} catch (err) {
console.error('[engagement-report] Error:', err.message);
return res.status(500).json({ error: 'Failed to generate engagement report' });
}
});
app.get('/api/admin/reports/careers', requireAdminAuth, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const days = parseInt(req.query.days) || 90;
const [careers] = await pool.execute(`
SELECT cv.career_name, cv.career_soc_code,
COUNT(DISTINCT cv.user_id) as unique_students,
COUNT(*) as total_views
FROM career_views cv
JOIN organization_students os ON cv.user_id = os.user_id
WHERE os.organization_id = ?
AND cv.viewed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
GROUP BY cv.career_soc_code, cv.career_name
ORDER BY unique_students DESC
LIMIT 50
`, [orgId, days]);
return res.json({ careers });
} catch (err) {
console.error('[career-trends] Error:', err.message);
return res.status(500).json({ error: 'Failed to generate career trends report' });
}
});
app.get('/api/admin/reports/export', requireAdminAuth, exportLimiter, async (req, res) => {
try {
const orgId = req.admin.organizationId;
const [students] = await pool.execute(`
SELECT os.id, os.enrollment_status, os.enrollment_date,
up.firstname, up.lastname, up.email, up.last_login, up.created_at,
sps.share_career_exploration, sps.share_interest_inventory,
sps.share_college_research, sps.share_financial_data, sps.share_premium_features
FROM organization_students os
JOIN user_profile up ON os.user_id = up.id
LEFT JOIN student_privacy_settings sps ON os.user_id = sps.user_id AND os.organization_id = sps.organization_id
WHERE os.organization_id = ?
ORDER BY up.lastname, up.firstname
`, [orgId]);
// Decrypt emails
for (const student of students) {
if (student.email) {
try {
student.email = decrypt(student.email);
} catch {
// Leave as-is
}
}
}
const headers = [
'Student Number', 'First Name', 'Last Name', 'Email',
'Enrollment Status', 'Enrollment Date', 'Last Login', 'Created At',
'Share Career', 'Share Inventory', 'Share College', 'Share Financial', 'Share Premium'
];
let csv = headers.join(',') + '\n';
for (let i = 0; i < students.length; i++) {
const student = students[i];
const row = [
i + 1, // Sequential student number instead of user_id
`"${student.firstname}"`,
`"${student.lastname}"`,
`"${student.email}"`,
student.enrollment_status,
student.enrollment_date,
student.last_login || '',
student.created_at,
student.share_career_exploration ? 'Y' : 'N',
student.share_interest_inventory ? 'Y' : 'N',
student.share_college_research ? 'Y' : 'N',
student.share_financial_data ? 'Y' : 'N',
student.share_premium_features ? 'Y' : 'N'
];
csv += row.join(',') + '\n';
}
res.setHeader('Content-Type', 'text/csv');
res.setHeader('Content-Disposition', `attachment; filename="students_export_${Date.now()}.csv"`);
return res.send(csv);
} catch (err) {
console.error('[export-students] Error:', err.message);
return res.status(500).json({ error: 'Failed to export student data' });
}
});
// ═══════════════════════════════════════════════════════════════════════════
// FINAL ERROR HANDLER
// ═══════════════════════════════════════════════════════════════════════════
app.use((err, req, res, _next) => {
if (res.headersSent) return;
const rid = req.headers['x-request-id'] || res.get('X-Request-ID') || getRequestId(req, res);
console.error(`[ref ${rid}]`, err?.message || err);
return res.status(500).json({ error: 'Server error', ref: rid });
});
// ═══════════════════════════════════════════════════════════════════════════
// START SERVER
// ═══════════════════════════════════════════════════════════════════════════
app.listen(PORT, '0.0.0.0', () => {
console.log(`✓ Admin API (server4) running on port ${PORT}`);
});