3142 lines
114 KiB
JavaScript
3142 lines
114 KiB
JavaScript
/**************************************************
|
||
* server4.js - Organization Admin Portal API
|
||
* Port: 5003
|
||
* Purpose: B2B admin endpoints for schools/colleges
|
||
**************************************************/
|
||
|
||
import express from 'express';
|
||
import helmet from 'helmet';
|
||
import dotenv from 'dotenv';
|
||
import path from 'path';
|
||
import { fileURLToPath } from 'url';
|
||
import pool from './config/mysqlPool.js';
|
||
import cookieParser from 'cookie-parser';
|
||
import jwt from 'jsonwebtoken';
|
||
import bcrypt from 'bcrypt';
|
||
import rateLimit from 'express-rate-limit';
|
||
import crypto from 'crypto';
|
||
import { readFile } from 'fs/promises';
|
||
import sgMail from '@sendgrid/mail';
|
||
import { initEncryption, encrypt, decrypt, verifyCanary, SENTINEL } from './shared/crypto/encryption.js';
|
||
import axios from 'axios';
|
||
|
||
const __filename = fileURLToPath(import.meta.url);
|
||
const __dirname = path.dirname(__filename);
|
||
const rootPath = path.resolve(__dirname, '..');
|
||
const env = (process.env.ENV_NAME === 'prod');
|
||
const envPath = path.resolve(rootPath, `.env.${env}`);
|
||
dotenv.config({ path: envPath, override: false });
|
||
|
||
const {
|
||
JWT_SECRET,
|
||
CORS_ALLOWED_ORIGINS,
|
||
SERVER4_PORT = 5003,
|
||
ADMIN_PORTAL_URL
|
||
} = process.env;
|
||
|
||
if (!JWT_SECRET) {
|
||
console.error('FATAL: JWT_SECRET missing – aborting startup');
|
||
process.exit(1);
|
||
}
|
||
if (!CORS_ALLOWED_ORIGINS) {
|
||
console.error('FATAL: CORS_ALLOWED_ORIGINS missing – aborting startup');
|
||
process.exit(1);
|
||
}
|
||
|
||
// Username lookup helper (HMAC-SHA256 hash for encrypted username querying)
|
||
function usernameLookup(username) {
|
||
const USERNAME_INDEX_KEY = process.env.USERNAME_INDEX_SECRET || JWT_SECRET;
|
||
return crypto
|
||
.createHmac('sha256', USERNAME_INDEX_KEY)
|
||
.update(String(username).trim().toLowerCase())
|
||
.digest('hex');
|
||
}
|
||
|
||
// SendGrid configuration (match server2.js exactly)
|
||
const SENDGRID_KEY = (process.env.SUPPORT_SENDGRID_API_KEY || '')
|
||
.trim()
|
||
.replace(/^['"]+|['"]+$/g, ''); // strip leading/trailing quotes if GCP injects them
|
||
|
||
if (SENDGRID_KEY) {
|
||
sgMail.setApiKey(SENDGRID_KEY);
|
||
console.log('[MAIL] SendGrid enabled');
|
||
} else {
|
||
console.warn('[MAIL] SUPPORT_SENDGRID_API_KEY missing/empty; invitation emails will be logged only');
|
||
}
|
||
|
||
const CANARY_SQL = `
|
||
CREATE TABLE IF NOT EXISTS encryption_canary (
|
||
id TINYINT NOT NULL PRIMARY KEY,
|
||
value TEXT NOT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`;
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// DEK BOOTSTRAP + CANARY VERIFICATION (BEFORE SERVING TRAFFIC)
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
try {
|
||
await initEncryption();
|
||
|
||
const db = pool.raw || pool;
|
||
|
||
// Quick connectivity check
|
||
await db.query('SELECT 1');
|
||
|
||
// Ensure canary table exists
|
||
await db.query(CANARY_SQL);
|
||
|
||
// Insert sentinel on first run (ignore if exists)
|
||
await db.query(
|
||
'INSERT IGNORE INTO encryption_canary (id, value) VALUES (1, ?)',
|
||
[encrypt(SENTINEL)]
|
||
);
|
||
|
||
// Read back & verify
|
||
const [rows] = await db.query(
|
||
'SELECT value FROM encryption_canary WHERE id = 1 LIMIT 1'
|
||
);
|
||
const plaintext = decrypt(rows[0]?.value || '');
|
||
if (plaintext !== SENTINEL) {
|
||
throw new Error('DEK mismatch with database sentinel');
|
||
}
|
||
|
||
console.log('[ENCRYPT] DEK verified against canary – proceeding');
|
||
} catch (err) {
|
||
console.error('FATAL:', err?.message || err);
|
||
process.exit(1);
|
||
}
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// LOAD ECONOMIC PROJECTIONS DATA
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
const DATA_DIR = path.join(__dirname, 'data');
|
||
const PROJ_FILE = path.join(DATA_DIR, 'economicproj.json');
|
||
|
||
let allProjections = [];
|
||
try {
|
||
const raw = await readFile(PROJ_FILE, 'utf8');
|
||
allProjections = JSON.parse(raw);
|
||
console.log(`[DATA] Loaded ${allProjections.length} economic projection rows`);
|
||
} catch (err) {
|
||
console.warn('[DATA] Failed to load economicproj.json:', err.message);
|
||
}
|
||
|
||
// Helper to get full state name from abbreviation
|
||
function fullStateFrom(s = '') {
|
||
const M = {
|
||
AL:'Alabama', AK:'Alaska', AZ:'Arizona', AR:'Arkansas', CA:'California', CO:'Colorado',
|
||
CT:'Connecticut', DE:'Delaware', DC:'District of Columbia', FL:'Florida', GA:'Georgia',
|
||
HI:'Hawaii', ID:'Idaho', IL:'Illinois', IN:'Indiana', IA:'Iowa', KS:'Kansas',
|
||
KY:'Kentucky', LA:'Louisiana', ME:'Maine', MD:'Maryland', MA:'Massachusetts',
|
||
MI:'Michigan', MN:'Minnesota', MS:'Mississippi', MO:'Missouri', MT:'Montana',
|
||
NE:'Nebraska', NV:'Nevada', NH:'New Hampshire', NJ:'New Jersey', NM:'New Mexico',
|
||
NY:'New York', NC:'North Carolina', ND:'North Dakota', OH:'Ohio', OK:'Oklahoma',
|
||
OR:'Oregon', PA:'Pennsylvania', RI:'Rhode Island', SC:'South Carolina',
|
||
SD:'South Dakota', TN:'Tennessee', TX:'Texas', UT:'Utah', VT:'Vermont',
|
||
VA:'Virginia', WA:'Washington', WV:'West Virginia', WI:'Wisconsin', WY:'Wyoming'
|
||
};
|
||
if (!s) return '';
|
||
const up = String(s).trim().toUpperCase();
|
||
return M[up] || s; // already full name → return as-is
|
||
}
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// EXPRESS APP & MIDDLEWARE
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
const app = express();
|
||
const PORT = process.env.SERVER4_PORT || 5003;
|
||
const ADMIN_COOKIE_NAME = 'aptiva_admin_session';
|
||
|
||
app.disable('x-powered-by');
|
||
app.use(express.json({ limit: '10mb' }));
|
||
app.set('trust proxy', 1); // behind proxy/HTTPS in all envs
|
||
app.use(cookieParser());
|
||
app.use(helmet({ contentSecurityPolicy: false, crossOriginEmbedderPolicy: false }));
|
||
|
||
app.use((req, res, next) => {
|
||
if (req.path.startsWith('/api/')) res.type('application/json');
|
||
next();
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// REQUEST ID + MINIMAL AUDIT LOG FOR /api/*
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
function getRequestId(req, res) {
|
||
const hdr = req.headers['x-request-id'];
|
||
if (typeof hdr === 'string' && hdr) return hdr; // from Nginx
|
||
const rid = crypto?.randomUUID?.() || `${Date.now().toString(36)}-${Math.random().toString(36).slice(2,8)}`;
|
||
res.setHeader('X-Request-ID', rid);
|
||
return rid;
|
||
}
|
||
|
||
app.use((req, res, next) => {
|
||
if (!req.path.startsWith('/api/')) return next();
|
||
|
||
const rid = getRequestId(req, res);
|
||
const t0 = process.hrtime.bigint();
|
||
|
||
res.on('finish', () => {
|
||
const durMs = Number((process.hrtime.bigint() - t0) / 1_000_000n);
|
||
const out = {
|
||
ts: new Date().toISOString(),
|
||
rid,
|
||
ip: req.ip || req.headers['x-forwarded-for'] || '',
|
||
method: req.method,
|
||
path: req.path,
|
||
status: res.statusCode,
|
||
dur_ms: durMs,
|
||
bytes_sent: Number(res.getHeader('Content-Length') || 0),
|
||
userId: req.userId || req.admin?.userId || null
|
||
};
|
||
try { console.log(JSON.stringify(out)); } catch {}
|
||
});
|
||
|
||
next();
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// DETAILED AUDIT LOGGING (REDACT SENSITIVE DATA)
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
function pickIp(req) {
|
||
return req.ip || req.headers['x-forwarded-for'] || req.socket?.remoteAddress || '';
|
||
}
|
||
|
||
function redactHeaders(h) {
|
||
const out = { ...h };
|
||
delete out.authorization;
|
||
delete out.cookie;
|
||
delete out['x-forwarded-for'];
|
||
return out;
|
||
}
|
||
|
||
function sampleBody(b) {
|
||
if (!b || typeof b !== 'object') return undefined;
|
||
const keys = Object.keys(b);
|
||
const preview = {};
|
||
for (const k of keys.slice(0, 12)) {
|
||
const v = b[k];
|
||
preview[k] = typeof v === 'string' ? (v.length > 80 ? v.slice(0, 80) + '…' : v) : (Array.isArray(v) ? `[array:${v.length}]` : typeof v);
|
||
}
|
||
return preview;
|
||
}
|
||
|
||
app.use((req, res, next) => {
|
||
if (!req.path.startsWith('/api/')) return next();
|
||
|
||
const rid = req.headers['x-request-id'] || crypto.randomUUID?.() || String(Date.now());
|
||
res.setHeader('X-Request-ID', rid);
|
||
const t0 = process.hrtime.bigint();
|
||
|
||
const reqLog = {
|
||
ts: new Date().toISOString(),
|
||
rid,
|
||
ip: pickIp(req),
|
||
method: req.method,
|
||
path: req.path,
|
||
userId: req.userId || req.admin?.userId || null,
|
||
ua: req.headers['user-agent'] || '',
|
||
hdr: redactHeaders(req.headers),
|
||
body: sampleBody(req.body)
|
||
};
|
||
|
||
res.on('finish', () => {
|
||
const durMs = Number((process.hrtime.bigint() - t0) / 1_000_000n);
|
||
const out = {
|
||
...reqLog,
|
||
status: res.statusCode,
|
||
dur_ms: durMs,
|
||
bytes_sent: Number(res.getHeader('Content-Length') || 0)
|
||
};
|
||
try { console.log(JSON.stringify(out)); } catch {}
|
||
});
|
||
|
||
next();
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// RUNTIME: NEVER CACHE API RESPONSES
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.use((req, res, next) => {
|
||
if (req.path.startsWith('/api/')) {
|
||
res.set('Cache-Control', 'no-store');
|
||
res.set('Pragma', 'no-cache');
|
||
res.set('Expires', '0');
|
||
}
|
||
next();
|
||
});
|
||
|
||
process.on('unhandledRejection', (e) => console.error('[unhandledRejection]', e));
|
||
process.on('uncaughtException', (e) => console.error('[uncaughtException]', e));
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// RUNTIME: ENFORCE JSON ON API WRITES (WITH NARROW EXCEPTIONS)
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
const MUST_JSON = new Set(['POST','PUT','PATCH']);
|
||
const EXEMPT_PATHS = [
|
||
// Add any multipart/form-data endpoints here if needed
|
||
];
|
||
|
||
app.use((req, res, next) => {
|
||
if (!req.path.startsWith('/api/')) return next();
|
||
if (!MUST_JSON.has(req.method)) return next();
|
||
if (EXEMPT_PATHS.some(rx => rx.test(req.path))) return next();
|
||
|
||
const ct = req.headers['content-type'] || '';
|
||
if (!ct.toLowerCase().includes('application/json')) {
|
||
return res.status(415).json({ error: 'unsupported_media_type' });
|
||
}
|
||
next();
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// RUNTIME PROTECTION: HPP GUARD (DEDUPE + CAP ARRAYS)
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.use((req, _res, next) => {
|
||
const MAX_ARRAY = 20;
|
||
|
||
const sanitize = (obj) => {
|
||
if (!obj || typeof obj !== 'object') return;
|
||
for (const k of Object.keys(obj)) {
|
||
const v = obj[k];
|
||
if (Array.isArray(v)) {
|
||
obj[k] = v.slice(0, MAX_ARRAY).filter(x => x !== '' && x != null);
|
||
if (obj[k].length === 1) obj[k] = obj[k][0];
|
||
}
|
||
}
|
||
};
|
||
|
||
sanitize(req.query);
|
||
sanitize(req.body);
|
||
next();
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// RUNTIME: REJECT REQUEST BODIES ON GET/HEAD
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.use((req, res, next) => {
|
||
if ((req.method === 'GET' || req.method === 'HEAD') && Number(req.headers['content-length'] || 0) > 0) {
|
||
return res.status(400).json({ error: 'no_body_allowed' });
|
||
}
|
||
next();
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// RUNTIME: LAST-RESORT ERROR SANITIZER
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.use((err, req, res, _next) => {
|
||
if (res.headersSent) return;
|
||
|
||
if (err?.code === 'LIMIT_FILE_SIZE') {
|
||
return res.status(413).json({ error: 'file_too_large', limit_mb: 10 });
|
||
}
|
||
if (err?.message && String(err.message).startsWith('blocked_outbound_host:')) {
|
||
return res.status(400).json({ error: 'blocked_outbound_host' });
|
||
}
|
||
if (err?.message === 'unsupported_type') {
|
||
return res.status(415).json({ error: 'unsupported_type' });
|
||
}
|
||
|
||
console.error('[unhandled]', err?.message || err);
|
||
return res.status(500).json({ error: 'Server error' });
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// CORS (STRICT ALLOWLIST FROM ENV)
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
const allowedOrigins = process.env.CORS_ALLOWED_ORIGINS
|
||
.split(',')
|
||
.map(o => o.trim())
|
||
.filter(Boolean);
|
||
|
||
app.use((req, res, next) => {
|
||
const origin = req.headers.origin || '';
|
||
if (!origin) return next();
|
||
if (!allowedOrigins.includes(origin)) {
|
||
return res.status(403).end();
|
||
}
|
||
res.setHeader('Access-Control-Allow-Origin', origin);
|
||
res.setHeader('Access-Control-Allow-Credentials', 'true');
|
||
res.setHeader(
|
||
'Access-Control-Allow-Headers',
|
||
'Authorization, Content-Type, Accept, Origin, X-Requested-With, Access-Control-Allow-Methods'
|
||
);
|
||
res.setHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, PATCH, DELETE, OPTIONS');
|
||
if (req.method === 'OPTIONS') return res.status(204).end();
|
||
return next();
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// SESSION COOKIE OPTIONS
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
function sessionCookieOptions() {
|
||
const IS_HTTPS = true;
|
||
const CROSS_SITE = process.env.CROSS_SITE_COOKIES === '1';
|
||
const COOKIE_DOMAIN = process.env.COOKIE_DOMAIN || undefined;
|
||
|
||
return {
|
||
httpOnly: true,
|
||
secure: IS_HTTPS,
|
||
sameSite: CROSS_SITE ? 'none' : 'lax',
|
||
path: '/',
|
||
maxAge: 8 * 60 * 60 * 1000, // 8 hours for admin sessions
|
||
...(COOKIE_DOMAIN ? { domain: COOKIE_DOMAIN } : {}),
|
||
};
|
||
}
|
||
|
||
function fprPathFromEnv() {
|
||
const p = (process.env.DEK_PATH || '').trim();
|
||
return p ? path.join(path.dirname(p), 'dek.fpr') : null;
|
||
}
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// HEALTH CHECK ENDPOINTS
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
// 1) Liveness: process is up and event loop responsive
|
||
app.get('/livez', (_req, res) => res.type('text').send('OK'));
|
||
|
||
// 2) Readiness: crypto + canary are good
|
||
app.get('/readyz', async (_req, res) => {
|
||
try {
|
||
await initEncryption();
|
||
await verifyCanary(pool);
|
||
return res.type('text').send('OK');
|
||
} catch (e) {
|
||
console.error('[READYZ]', e.message);
|
||
return res.status(500).type('text').send('FAIL');
|
||
}
|
||
});
|
||
|
||
// 3) Health: detailed JSON
|
||
app.get('/healthz', async (_req, res) => {
|
||
const out = {
|
||
service: 'server4-admin-api',
|
||
version: process.env.IMG_TAG || null,
|
||
uptime_s: Math.floor(process.uptime()),
|
||
now: new Date().toISOString(),
|
||
checks: {
|
||
live: { ok: true },
|
||
crypto: { ok: false, fp: null },
|
||
db: { ok: false, ping_ms: null },
|
||
canary: { ok: false }
|
||
}
|
||
};
|
||
|
||
// crypto / DEK
|
||
try {
|
||
await initEncryption();
|
||
out.checks.crypto.ok = true;
|
||
const p = fprPathFromEnv();
|
||
if (p) {
|
||
try { out.checks.crypto.fp = (await readFile(p, 'utf8')).trim(); }
|
||
catch { /* fp optional */ }
|
||
}
|
||
} catch (e) {
|
||
out.checks.crypto.error = e.message;
|
||
}
|
||
|
||
// DB ping
|
||
const t0 = Date.now();
|
||
try {
|
||
await pool.query('SELECT 1');
|
||
out.checks.db.ok = true;
|
||
out.checks.db.ping_ms = Date.now() - t0;
|
||
} catch (e) {
|
||
out.checks.db.error = e.message;
|
||
}
|
||
|
||
// canary
|
||
try {
|
||
await verifyCanary(pool);
|
||
out.checks.canary.ok = true;
|
||
} catch (e) {
|
||
out.checks.canary.error = e.message;
|
||
}
|
||
|
||
const ready = out.checks.crypto.ok && out.checks.db.ok && out.checks.canary.ok;
|
||
return res.status(ready ? 200 : 503).json(out);
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// RATE LIMITERS
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
const adminLoginLimiter = rateLimit({
|
||
windowMs: 15 * 60 * 1000,
|
||
max: 10,
|
||
standardHeaders: true,
|
||
legacyHeaders: false,
|
||
keyGenerator: (req) => req.ip
|
||
});
|
||
|
||
const bulkUploadLimiter = rateLimit({
|
||
windowMs: 60 * 1000,
|
||
max: 5,
|
||
standardHeaders: true,
|
||
legacyHeaders: false,
|
||
keyGenerator: (req) => req.ip
|
||
});
|
||
|
||
const rosterUploadLimiter = rateLimit({
|
||
windowMs: 60 * 1000,
|
||
max: 3,
|
||
standardHeaders: true,
|
||
legacyHeaders: false,
|
||
keyGenerator: (req) => req.ip
|
||
});
|
||
|
||
const exportLimiter = rateLimit({
|
||
windowMs: 60 * 1000,
|
||
max: 10,
|
||
standardHeaders: true,
|
||
legacyHeaders: false,
|
||
keyGenerator: (req) => req.ip
|
||
});
|
||
|
||
const resendLimiter = rateLimit({
|
||
windowMs: 60 * 1000,
|
||
max: 20,
|
||
standardHeaders: true,
|
||
legacyHeaders: false,
|
||
keyGenerator: (req) => req.ip
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// EMAIL INVITATION HELPER
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
// Send invitation to NEW users (no existing AptivaAI account)
|
||
async function sendNewUserInvitation(email, firstname, organizationName, organizationId, userId) {
|
||
const inviteToken = jwt.sign(
|
||
{
|
||
email,
|
||
userId,
|
||
organizationId,
|
||
prp: 'student_invite',
|
||
isNewUser: true
|
||
},
|
||
JWT_SECRET,
|
||
{ expiresIn: '7d' }
|
||
);
|
||
|
||
const MAIN_APP_URL = process.env.APTIVA_API_BASE || 'https://aptivaai.com';
|
||
const inviteLink = `${MAIN_APP_URL}/signup?invite=${inviteToken}`;
|
||
const linkAccountLink = `${MAIN_APP_URL}/link-secondary-email?token=${inviteToken}`;
|
||
|
||
const text = `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.
|
||
|
||
Click the link below to create your account (valid for 7 days):
|
||
${inviteLink}
|
||
|
||
Already have an AptivaAI account with a different email? Link it here:
|
||
${linkAccountLink}
|
||
|
||
Questions? Reply to this email or contact your administrator.
|
||
|
||
— The AptivaAI Team`;
|
||
|
||
const html = `<div style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; max-width: 600px; margin: 0 auto; padding: 20px;">
|
||
<h2 style="color: #1e40af;">Welcome to AptivaAI!</h2>
|
||
<p>Hi ${firstname},</p>
|
||
<p>You've been invited to join <strong>${organizationName}</strong> on AptivaAI!</p>
|
||
<p>AptivaAI is a career exploration platform that helps you discover potential career paths, explore educational programs, and plan your professional future.</p>
|
||
<p style="margin: 30px 0; text-align: center;">
|
||
<a href="${inviteLink}" style="background-color: #1e40af; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block;">
|
||
Create Your Account
|
||
</a>
|
||
</p>
|
||
<div style="background-color: #f9fafb; border: 1px solid #e5e7eb; border-radius: 8px; padding: 16px; margin: 20px 0;">
|
||
<p style="margin: 0 0 8px 0; font-size: 14px; color: #374151;">
|
||
<strong>Already have an AptivaAI account with a different email?</strong>
|
||
</p>
|
||
<p style="margin: 0; font-size: 14px;">
|
||
<a href="${linkAccountLink}" style="color: #1e40af; text-decoration: underline;">
|
||
Click here to link your existing account
|
||
</a>
|
||
</p>
|
||
</div>
|
||
<p style="color: #6b7280; font-size: 14px;">This invitation link is valid for 7 days.</p>
|
||
<p style="color: #6b7280; font-size: 14px;">Questions? Reply to this email or contact your administrator.</p>
|
||
<hr style="border: none; border-top: 1px solid #e5e7eb; margin: 30px 0;" />
|
||
<p style="color: #9ca3af; font-size: 12px;">— The AptivaAI Team</p>
|
||
</div>`;
|
||
|
||
if (SENDGRID_KEY) {
|
||
await sgMail.send({
|
||
to: email,
|
||
from: 'no-reply@aptivaai.com',
|
||
subject: `You're invited to ${organizationName} on AptivaAI`,
|
||
text,
|
||
html
|
||
});
|
||
console.log(`[INVITE] Sent new user invitation email to ${email}`);
|
||
} else {
|
||
console.log(`[INVITE] SendGrid disabled. Would send new user invite to ${email}:`);
|
||
console.log(`[INVITE] Link: ${inviteLink}`);
|
||
}
|
||
}
|
||
|
||
// Send invitation to EXISTING users (already have AptivaAI account)
|
||
async function sendExistingUserInvitation(email, firstname, organizationName, organizationId, userId) {
|
||
const inviteToken = jwt.sign(
|
||
{
|
||
email,
|
||
userId,
|
||
organizationId,
|
||
prp: 'student_invite',
|
||
isNewUser: false
|
||
},
|
||
JWT_SECRET,
|
||
{ expiresIn: '7d' }
|
||
);
|
||
|
||
const MAIN_APP_URL = process.env.APTIVA_API_BASE || 'https://aptivaai.com';
|
||
const inviteLink = `${MAIN_APP_URL}/invite-response?token=${inviteToken}`;
|
||
|
||
const text = `Hi ${firstname},
|
||
|
||
You've been invited to join ${organizationName} on AptivaAI!
|
||
|
||
We noticed you already have an AptivaAI account. You have two options:
|
||
|
||
1. Link your existing account to ${organizationName}
|
||
- Your existing data and progress will be preserved
|
||
- ${organizationName} can view your activity based on your privacy settings
|
||
- You'll gain premium access through ${organizationName}
|
||
|
||
2. Create a separate account for ${organizationName}
|
||
- Keep your personal AptivaAI account separate
|
||
- Start fresh with a new profile for school/organization use
|
||
|
||
Click the link below to choose (valid for 7 days):
|
||
${inviteLink}
|
||
|
||
Questions? Reply to this email or contact your administrator.
|
||
|
||
— The AptivaAI Team`;
|
||
|
||
const html = `<div style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; max-width: 600px; margin: 0 auto; padding: 20px;">
|
||
<h2 style="color: #1e40af;">You're Invited to Join ${organizationName}!</h2>
|
||
<p>Hi ${firstname},</p>
|
||
<p>You've been invited to join <strong>${organizationName}</strong> on AptivaAI!</p>
|
||
|
||
<div style="background-color: #eff6ff; border-left: 4px solid #1e40af; padding: 16px; margin: 20px 0;">
|
||
<p style="margin: 0; color: #1e40af; font-weight: 600;">We noticed you already have an AptivaAI account</p>
|
||
</div>
|
||
|
||
<p style="font-weight: 600; margin-top: 24px;">You have two options:</p>
|
||
|
||
<div style="background-color: #f9fafb; border: 1px solid #e5e7eb; border-radius: 8px; padding: 16px; margin: 16px 0;">
|
||
<p style="margin: 0 0 8px 0; font-weight: 600; color: #111827;">Option 1: Link Your Existing Account</p>
|
||
<ul style="margin: 0; padding-left: 20px; color: #4b5563;">
|
||
<li>Your existing data and progress will be preserved</li>
|
||
<li>${organizationName} can view your activity based on your privacy settings</li>
|
||
<li>You'll gain premium access through ${organizationName}</li>
|
||
</ul>
|
||
</div>
|
||
|
||
<div style="background-color: #f9fafb; border: 1px solid #e5e7eb; border-radius: 8px; padding: 16px; margin: 16px 0;">
|
||
<p style="margin: 0 0 8px 0; font-weight: 600; color: #111827;">Option 2: Create a Separate Account</p>
|
||
<ul style="margin: 0; padding-left: 20px; color: #4b5563;">
|
||
<li>Keep your personal AptivaAI account separate</li>
|
||
<li>Start fresh with a new profile for school/organization use</li>
|
||
</ul>
|
||
</div>
|
||
|
||
<p style="margin: 30px 0; text-align: center;">
|
||
<a href="${inviteLink}" style="background-color: #1e40af; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block;">
|
||
Choose Your Option
|
||
</a>
|
||
</p>
|
||
|
||
<p style="color: #6b7280; font-size: 14px;">This invitation link is valid for 7 days.</p>
|
||
<p style="color: #6b7280; font-size: 14px;">Questions? Reply to this email or contact your administrator.</p>
|
||
<hr style="border: none; border-top: 1px solid #e5e7eb; margin: 30px 0;" />
|
||
<p style="color: #9ca3af; font-size: 12px;">— The AptivaAI Team</p>
|
||
</div>`;
|
||
|
||
if (SENDGRID_KEY) {
|
||
await sgMail.send({
|
||
to: email,
|
||
from: 'no-reply@aptivaai.com',
|
||
subject: `You're invited to ${organizationName} on AptivaAI`,
|
||
text,
|
||
html
|
||
});
|
||
console.log(`[INVITE] Sent existing user invitation email to ${email}`);
|
||
} else {
|
||
console.log(`[INVITE] SendGrid disabled. Would send existing user invite to ${email}:`);
|
||
console.log(`[INVITE] Link: ${inviteLink}`);
|
||
}
|
||
}
|
||
|
||
// Legacy function - redirects to appropriate function based on isNewUser flag
|
||
async function sendStudentInvitation(email, firstname, organizationName, organizationId, userId, isNewUser = true) {
|
||
if (isNewUser) {
|
||
return sendNewUserInvitation(email, firstname, organizationName, organizationId, userId);
|
||
} else {
|
||
return sendExistingUserInvitation(email, firstname, organizationName, organizationId, userId);
|
||
}
|
||
}
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// VALIDATE INVITATION TOKEN (PUBLIC ENDPOINT)
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.post('/api/validate-invite', async (req, res) => {
|
||
const { token } = req.body;
|
||
|
||
if (!token) {
|
||
return res.status(400).json({ error: 'Invitation token required' });
|
||
}
|
||
|
||
try {
|
||
const decoded = jwt.verify(token, JWT_SECRET);
|
||
|
||
if (decoded.prp !== 'student_invite') {
|
||
return res.status(400).json({ error: 'Invalid invitation token' });
|
||
}
|
||
|
||
// Verify user still exists and is pending
|
||
const [users] = await pool.execute(
|
||
'SELECT id, email, firstname, username FROM user_profile WHERE id = ? LIMIT 1',
|
||
[decoded.userId]
|
||
);
|
||
|
||
if (!users.length) {
|
||
return res.status(404).json({ error: 'Invitation not found or expired' });
|
||
}
|
||
|
||
const user = users[0];
|
||
|
||
// Check if already completed signup (username is not NULL)
|
||
if (user.username) {
|
||
return res.status(400).json({ error: 'This invitation has already been used. Please sign in instead.' });
|
||
}
|
||
|
||
// Verify enrollment exists and is pending
|
||
const [enrollment] = await pool.execute(
|
||
'SELECT enrollment_status FROM organization_students WHERE organization_id = ? AND user_id = ?',
|
||
[decoded.organizationId, decoded.userId]
|
||
);
|
||
|
||
if (!enrollment.length) {
|
||
return res.status(404).json({ error: 'Invitation not found' });
|
||
}
|
||
|
||
// Decrypt email for pre-fill
|
||
let email = user.email;
|
||
try {
|
||
email = decrypt(email);
|
||
} catch (err) {
|
||
// Not encrypted or decryption failed
|
||
}
|
||
|
||
return res.json({
|
||
valid: true,
|
||
email: email,
|
||
userId: decoded.userId,
|
||
organizationId: decoded.organizationId
|
||
});
|
||
} catch (err) {
|
||
if (err.name === 'TokenExpiredError') {
|
||
return res.status(400).json({ error: 'Invitation link has expired. Please contact your administrator.' });
|
||
}
|
||
console.error('[validate-invite] Error:', err.message);
|
||
return res.status(400).json({ error: 'Invalid invitation token' });
|
||
}
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// AUTH MIDDLEWARE
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
async function requireAdminAuth(req, res, next) {
|
||
const token = req.cookies[ADMIN_COOKIE_NAME];
|
||
|
||
if (!token) {
|
||
return res.status(401).json({ error: 'Admin authentication required' });
|
||
}
|
||
|
||
try {
|
||
const decoded = jwt.verify(token, JWT_SECRET);
|
||
|
||
const [admins] = await pool.execute(`
|
||
SELECT oa.id, oa.organization_id, oa.user_id, oa.role,
|
||
o.organization_name, o.organization_type
|
||
FROM organization_admins oa
|
||
JOIN organizations o ON oa.organization_id = o.id
|
||
WHERE oa.user_id = ?
|
||
LIMIT 1
|
||
`, [decoded.userId]);
|
||
|
||
if (!admins || admins.length === 0) {
|
||
return res.status(403).json({ error: 'Admin access not found' });
|
||
}
|
||
|
||
const admin = admins[0];
|
||
|
||
req.admin = {
|
||
userId: admin.user_id,
|
||
organizationId: admin.organization_id,
|
||
organizationName: admin.organization_name,
|
||
organizationType: admin.organization_type,
|
||
role: admin.role,
|
||
isSuperAdmin: admin.role === 'super_admin'
|
||
};
|
||
|
||
next();
|
||
} catch (err) {
|
||
console.error('[requireAdminAuth] Error:', err.message);
|
||
return res.status(401).json({ error: 'Invalid or expired admin session' });
|
||
}
|
||
}
|
||
|
||
function requireSuperAdmin(req, res, next) {
|
||
if (!req.admin || !req.admin.isSuperAdmin) {
|
||
return res.status(403).json({ error: 'Super admin access required' });
|
||
}
|
||
next();
|
||
}
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// AUTHENTICATION ENDPOINTS
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.post('/api/admin/auth/login', adminLoginLimiter, async (req, res) => {
|
||
const { username, password } = req.body;
|
||
|
||
if (!username || !password) {
|
||
return res.status(400).json({ error: 'Username and password required' });
|
||
}
|
||
|
||
try {
|
||
// Use username_lookup hash for querying (username is encrypted)
|
||
const usernameLookupVal = usernameLookup(username);
|
||
|
||
const [authResults] = await pool.execute(`
|
||
SELECT ua.user_id, ua.hashed_password
|
||
FROM user_auth ua
|
||
WHERE ua.username_lookup = ?
|
||
LIMIT 1
|
||
`, [usernameLookupVal]);
|
||
|
||
if (!authResults || authResults.length === 0) {
|
||
return res.status(401).json({ error: 'Invalid credentials' });
|
||
}
|
||
|
||
const { user_id, hashed_password } = authResults[0];
|
||
const isMatch = await bcrypt.compare(password, hashed_password);
|
||
|
||
if (!isMatch) {
|
||
return res.status(401).json({ error: 'Invalid credentials' });
|
||
}
|
||
|
||
const [adminResults] = await pool.execute(`
|
||
SELECT oa.id, oa.organization_id, oa.role,
|
||
o.organization_name, o.organization_type
|
||
FROM organization_admins oa
|
||
JOIN organizations o ON oa.organization_id = o.id
|
||
WHERE oa.user_id = ?
|
||
LIMIT 1
|
||
`, [user_id]);
|
||
|
||
if (!adminResults || adminResults.length === 0) {
|
||
return res.status(403).json({ error: 'Not authorized as organization admin' });
|
||
}
|
||
|
||
const admin = adminResults[0];
|
||
|
||
const token = jwt.sign(
|
||
{ userId: user_id, organizationId: admin.organization_id },
|
||
JWT_SECRET,
|
||
{ expiresIn: '8h' }
|
||
);
|
||
|
||
res.cookie(ADMIN_COOKIE_NAME, token, sessionCookieOptions());
|
||
|
||
return res.json({
|
||
message: 'Login successful',
|
||
admin: {
|
||
organizationName: admin.organization_name,
|
||
organizationType: admin.organization_type,
|
||
role: admin.role
|
||
}
|
||
});
|
||
} catch (err) {
|
||
console.error('[admin-login] Error:', err.message);
|
||
return res.status(500).json({ error: 'Login failed' });
|
||
}
|
||
});
|
||
|
||
app.post('/api/admin/auth/logout', (req, res) => {
|
||
res.clearCookie(ADMIN_COOKIE_NAME, sessionCookieOptions());
|
||
return res.json({ message: 'Logged out successfully' });
|
||
});
|
||
|
||
app.get('/api/admin/auth/me', requireAdminAuth, (req, res) => {
|
||
return res.json({
|
||
organizationId: req.admin.organizationId,
|
||
organizationName: req.admin.organizationName,
|
||
organizationType: req.admin.organizationType,
|
||
role: req.admin.role,
|
||
isSuperAdmin: req.admin.isSuperAdmin
|
||
});
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// DASHBOARD ENDPOINTS
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/admin/dashboard/stats', requireAdminAuth, async (req, res) => {
|
||
try {
|
||
const orgId = req.admin.organizationId;
|
||
|
||
const [totalResult] = await pool.execute(`
|
||
SELECT COUNT(*) as count
|
||
FROM organization_students
|
||
WHERE organization_id = ?
|
||
AND enrollment_status NOT IN ('withdrawn', 'transferred', 'inactive')
|
||
`, [orgId]);
|
||
|
||
const [activeResult] = await pool.execute(`
|
||
SELECT COUNT(*) as count
|
||
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 30 DAY)
|
||
`, [orgId]);
|
||
|
||
const [rosterHistory] = await pool.execute(`
|
||
SELECT submitted_at, students_added, total_roster_size
|
||
FROM roster_updates
|
||
WHERE organization_id = ?
|
||
ORDER BY submitted_at DESC
|
||
LIMIT 3
|
||
`, [orgId]);
|
||
|
||
const [adminCount] = await pool.execute(`
|
||
SELECT COUNT(*) as count
|
||
FROM organization_admins
|
||
WHERE organization_id = ?
|
||
`, [orgId]);
|
||
|
||
// Get organization's state and metro area for BLS data queries
|
||
let orgState = 'US';
|
||
let orgStateAbbr = 'US';
|
||
let orgMetro = null;
|
||
let topPayingCareers = [];
|
||
let fastestGrowingCareers = [];
|
||
let mostJobsCareers = [];
|
||
|
||
try {
|
||
const [orgData] = await pool.execute(`
|
||
SELECT state, metro_area FROM organizations WHERE id = ? LIMIT 1
|
||
`, [orgId]);
|
||
|
||
orgStateAbbr = orgData[0]?.state || 'US';
|
||
orgState = fullStateFrom(orgStateAbbr);
|
||
orgMetro = orgData[0]?.metro_area;
|
||
|
||
// Get highest paying careers - use projection data to get career list, then fetch salaries
|
||
const salaryLocation = orgMetro || orgState;
|
||
try {
|
||
const stateName = orgState.toLowerCase();
|
||
|
||
// Get all careers for this state from projections data
|
||
const stateCareers = allProjections
|
||
.filter(r => {
|
||
const area = String(r['Area Name'] || '').trim().toLowerCase();
|
||
return area === stateName;
|
||
})
|
||
.map(r => ({
|
||
soc_code: r['Occupation Code'],
|
||
career_name: r['Occupation Name']
|
||
}));
|
||
|
||
// Deduplicate by SOC code (some may appear multiple times)
|
||
const uniqueCareers = Array.from(new Map(stateCareers.map(c => [c.soc_code, c])).values());
|
||
|
||
// Sample top 50 careers to check salaries (to avoid too many API calls)
|
||
const careersToCheck = uniqueCareers.slice(0, 50);
|
||
|
||
// For each career, fetch salary data from server2
|
||
const salaryPromises = careersToCheck.map(async (career) => {
|
||
try {
|
||
const resp = await axios.get('http://server2:5001/api/salary', {
|
||
params: {
|
||
socCode: career.soc_code,
|
||
area: salaryLocation
|
||
},
|
||
timeout: 2000
|
||
});
|
||
const median = resp.data?.regional?.regional_MEDIAN || resp.data?.national?.national_MEDIAN;
|
||
if (median) {
|
||
return {
|
||
career_name: career.career_name,
|
||
median_salary: median,
|
||
soc_code: career.soc_code
|
||
};
|
||
}
|
||
} catch (err) {
|
||
// Skip careers with no salary data
|
||
}
|
||
return null;
|
||
});
|
||
|
||
const salaryResults = (await Promise.all(salaryPromises))
|
||
.filter(x => x !== null)
|
||
.sort((a, b) => b.median_salary - a.median_salary)
|
||
.slice(0, 5);
|
||
|
||
topPayingCareers = salaryResults;
|
||
} catch (salaryErr) {
|
||
console.error('[dashboard-stats] BLS salary query failed:', salaryErr.message);
|
||
}
|
||
|
||
// Get fastest growing careers by state (by growth rate %)
|
||
try {
|
||
const stateName = orgState.toLowerCase();
|
||
const stateProjections = allProjections.filter(r => {
|
||
const area = String(r['Area Name'] || '').trim().toLowerCase();
|
||
return area === stateName;
|
||
});
|
||
|
||
// Sort by percent change descending
|
||
const sorted = stateProjections
|
||
.map(r => ({
|
||
career_name: r['Occupation Name'],
|
||
growth_rate: parseFloat(r['Percent Change']) || 0,
|
||
soc_code: r['Occupation Code']
|
||
}))
|
||
.sort((a, b) => b.growth_rate - a.growth_rate)
|
||
.slice(0, 5);
|
||
|
||
fastestGrowingCareers = sorted;
|
||
} catch (growthErr) {
|
||
console.error('[dashboard-stats] BLS projections query failed:', growthErr.message);
|
||
}
|
||
|
||
// Get careers with most projected jobs (total job count in 10 years)
|
||
try {
|
||
const stateName = orgState.toLowerCase();
|
||
const stateProjections = allProjections.filter(r => {
|
||
const area = String(r['Area Name'] || '').trim().toLowerCase();
|
||
return area === stateName;
|
||
});
|
||
|
||
// Sort by projection (total jobs in 10 years) descending
|
||
const sorted = stateProjections
|
||
.map(r => ({
|
||
career_name: r['Occupation Name'],
|
||
projected_jobs: parseInt(r['Projection']) || 0,
|
||
soc_code: r['Occupation Code']
|
||
}))
|
||
.filter(x => x.projected_jobs > 0)
|
||
.sort((a, b) => b.projected_jobs - a.projected_jobs)
|
||
.slice(0, 5);
|
||
|
||
mostJobsCareers = sorted;
|
||
} catch (jobsErr) {
|
||
console.error('[dashboard-stats] BLS jobs projections query failed:', jobsErr.message);
|
||
}
|
||
} catch (orgErr) {
|
||
console.error('[dashboard-stats] Organization query failed:', orgErr.message);
|
||
}
|
||
|
||
// Get roster update reminders based on academic calendar
|
||
let rosterReminders = [];
|
||
try {
|
||
const [calendarData] = await pool.execute(`
|
||
SELECT calendar_type, fall_term_start_month, fall_term_start_day, fall_add_drop_deadline_days,
|
||
winter_term_start_month, winter_term_start_day, winter_add_drop_deadline_days,
|
||
spring_term_start_month, spring_term_start_day, spring_add_drop_deadline_days,
|
||
summer_term_start_month, summer_term_start_day, summer_add_drop_deadline_days
|
||
FROM academic_calendars
|
||
WHERE organization_id = ?
|
||
LIMIT 1
|
||
`, [orgId]);
|
||
|
||
if (calendarData && calendarData.length > 0) {
|
||
const calendar = calendarData[0];
|
||
const today = new Date();
|
||
const currentYear = today.getFullYear();
|
||
|
||
// Helper to calculate deadline date
|
||
const getDeadlineDate = (month, day, offsetDays, year = currentYear) => {
|
||
const termStart = new Date(year, month - 1, day);
|
||
const deadline = new Date(termStart);
|
||
deadline.setDate(deadline.getDate() + offsetDays);
|
||
return deadline;
|
||
};
|
||
|
||
// Check each term based on calendar type
|
||
const terms = [];
|
||
|
||
// Fall term (all types)
|
||
if (calendar.fall_term_start_month) {
|
||
const fallDeadline = getDeadlineDate(calendar.fall_term_start_month, calendar.fall_term_start_day, calendar.fall_add_drop_deadline_days || 14);
|
||
terms.push({ name: 'Fall', deadline: fallDeadline });
|
||
}
|
||
|
||
// Winter term (quarter/trimester)
|
||
if (calendar.winter_term_start_month && ['quarter', 'trimester'].includes(calendar.calendar_type)) {
|
||
const winterDeadline = getDeadlineDate(calendar.winter_term_start_month, calendar.winter_term_start_day, calendar.winter_add_drop_deadline_days || 14);
|
||
terms.push({ name: 'Winter', deadline: winterDeadline });
|
||
}
|
||
|
||
// Spring term (all types)
|
||
if (calendar.spring_term_start_month) {
|
||
const springDeadline = getDeadlineDate(calendar.spring_term_start_month, calendar.spring_term_start_day, calendar.spring_add_drop_deadline_days || 14);
|
||
terms.push({ name: 'Spring', deadline: springDeadline });
|
||
}
|
||
|
||
// Summer term (quarter only)
|
||
if (calendar.summer_term_start_month && calendar.calendar_type === 'quarter') {
|
||
const summerDeadline = getDeadlineDate(calendar.summer_term_start_month, calendar.summer_term_start_day, calendar.summer_add_drop_deadline_days || 7);
|
||
terms.push({ name: 'Summer', deadline: summerDeadline });
|
||
}
|
||
|
||
// Find upcoming deadlines
|
||
for (const term of terms) {
|
||
const daysUntilDeadline = Math.ceil((term.deadline - today) / (1000 * 60 * 60 * 24));
|
||
|
||
if (daysUntilDeadline > 0 && daysUntilDeadline <= 30) {
|
||
rosterReminders.push({
|
||
term: term.name,
|
||
deadline: term.deadline.toISOString().split('T')[0],
|
||
daysUntil: daysUntilDeadline,
|
||
status: 'due_soon'
|
||
});
|
||
} else if (daysUntilDeadline < 0 && daysUntilDeadline >= -7) {
|
||
rosterReminders.push({
|
||
term: term.name,
|
||
deadline: term.deadline.toISOString().split('T')[0],
|
||
daysUntil: Math.abs(daysUntilDeadline),
|
||
status: 'overdue'
|
||
});
|
||
}
|
||
}
|
||
}
|
||
} catch (reminderErr) {
|
||
console.error('[dashboard-stats] Roster reminders query failed:', reminderErr.message);
|
||
}
|
||
|
||
// Career Outcome Linkage Metrics
|
||
let careerOutcomeMetrics = {
|
||
studentsWithCareerProfiles: 0,
|
||
careerProfilesMatchingExploration: 0,
|
||
careerProfilesMatchingCareerComparison: 0,
|
||
studentsWithCollegeProfiles: 0
|
||
};
|
||
|
||
try {
|
||
// Count students with career profiles
|
||
const [careerProfileCount] = await pool.execute(`
|
||
SELECT COUNT(DISTINCT cp.user_id) as count
|
||
FROM career_profiles cp
|
||
JOIN organization_students os ON cp.user_id = os.user_id
|
||
WHERE os.organization_id = ? AND os.enrollment_status = 'active'
|
||
`, [orgId]);
|
||
careerOutcomeMetrics.studentsWithCareerProfiles = careerProfileCount[0].count;
|
||
|
||
// Count career profiles that match careers students explored (career_views)
|
||
// Match on SOC code for accuracy (career names may vary)
|
||
const [explorationMatch] = await pool.execute(`
|
||
SELECT COUNT(DISTINCT cp.user_id) as count
|
||
FROM career_profiles cp
|
||
JOIN organization_students os ON cp.user_id = os.user_id
|
||
JOIN career_views cv ON cp.user_id = cv.user_id
|
||
AND cp.career_soc_code COLLATE utf8mb4_unicode_ci = cv.career_soc_code COLLATE utf8mb4_unicode_ci
|
||
WHERE os.organization_id = ?
|
||
AND os.enrollment_status = 'active'
|
||
AND cp.career_soc_code IS NOT NULL
|
||
`, [orgId]);
|
||
careerOutcomeMetrics.careerProfilesMatchingExploration = explorationMatch[0].count;
|
||
|
||
// Count career profiles that match careers in Career Comparison (career_list)
|
||
// Note: career_list is encrypted, must decrypt before parsing
|
||
const [studentsWithProfiles] = await pool.execute(`
|
||
SELECT DISTINCT cp.user_id, cp.career_soc_code, up.career_list
|
||
FROM career_profiles cp
|
||
JOIN organization_students os ON cp.user_id = os.user_id
|
||
JOIN user_profile up ON cp.user_id = up.id
|
||
WHERE os.organization_id = ?
|
||
AND os.enrollment_status = 'active'
|
||
AND up.career_list IS NOT NULL
|
||
AND cp.career_soc_code IS NOT NULL
|
||
`, [orgId]);
|
||
|
||
let comparisonMatchCount = 0;
|
||
for (const row of studentsWithProfiles) {
|
||
try {
|
||
const decryptedList = decrypt(row.career_list);
|
||
const careerList = JSON.parse(decryptedList);
|
||
if (Array.isArray(careerList) && careerList.some(c => c.code === row.career_soc_code)) {
|
||
comparisonMatchCount++;
|
||
}
|
||
} catch (err) {
|
||
// Skip if decrypt/parse fails
|
||
}
|
||
}
|
||
careerOutcomeMetrics.careerProfilesMatchingCareerComparison = comparisonMatchCount;
|
||
|
||
// Count students with college profiles
|
||
const [collegeProfileCount] = await pool.execute(`
|
||
SELECT COUNT(DISTINCT cp.user_id) as count
|
||
FROM college_profiles cp
|
||
JOIN organization_students os ON cp.user_id = os.user_id
|
||
WHERE os.organization_id = ?
|
||
AND os.enrollment_status = 'active'
|
||
`, [orgId]);
|
||
careerOutcomeMetrics.studentsWithCollegeProfiles = collegeProfileCount[0].count;
|
||
|
||
// Note: Program alignment requires CIP-to-SOC mapping which is in server2
|
||
// For now, just track college profile creation
|
||
|
||
} catch (outcomeErr) {
|
||
console.error('[dashboard-stats] Career outcome metrics failed:', outcomeErr.message);
|
||
}
|
||
|
||
return res.json({
|
||
totalStudents: totalResult[0].count,
|
||
activeStudents: activeResult[0].count,
|
||
recentUploads: rosterHistory,
|
||
adminCount: adminCount[0].count,
|
||
topPayingCareers: topPayingCareers,
|
||
fastestGrowingCareers: fastestGrowingCareers,
|
||
mostJobsCareers: mostJobsCareers,
|
||
orgState: orgState,
|
||
orgMetro: orgMetro,
|
||
rosterReminders: rosterReminders,
|
||
careerOutcomes: careerOutcomeMetrics
|
||
});
|
||
} catch (err) {
|
||
console.error('[dashboard-stats] Error:', err.message);
|
||
return res.status(500).json({ error: 'Failed to load dashboard stats' });
|
||
}
|
||
});
|
||
|
||
// Student Career Interests with filters
|
||
app.get('/api/admin/dashboard/career-interests', requireAdminAuth, async (req, res) => {
|
||
try {
|
||
const orgId = req.admin.organizationId;
|
||
const { signalStrength = 'viewed', timePeriod = '90', limit = '10' } = req.query;
|
||
const limitNum = Math.min(parseInt(limit) || 10, 100); // Max 100, default 10
|
||
|
||
// Map time period to days
|
||
const dayMap = {
|
||
'30': 30,
|
||
'90': 90,
|
||
'365': 365,
|
||
'all': 9999
|
||
};
|
||
const days = dayMap[timePeriod] || 90;
|
||
|
||
let query, params;
|
||
|
||
// Build query based on signal strength
|
||
switch (signalStrength) {
|
||
case 'viewed':
|
||
query = `
|
||
SELECT cv.career_name, cv.career_soc_code, COUNT(DISTINCT cv.user_id) as student_count
|
||
FROM career_views cv
|
||
JOIN organization_students os ON cv.user_id = os.user_id
|
||
WHERE os.organization_id = ?
|
||
AND os.enrollment_status = 'active'
|
||
${days < 9999 ? 'AND cv.viewed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)' : ''}
|
||
GROUP BY cv.career_soc_code, cv.career_name
|
||
ORDER BY student_count DESC
|
||
LIMIT ?
|
||
`;
|
||
params = days < 9999 ? [orgId, days, limitNum] : [orgId, limitNum];
|
||
break;
|
||
|
||
case 'compared':
|
||
// career_list is encrypted, must fetch and parse in app code
|
||
query = `
|
||
SELECT up.id as user_id, up.career_list, up.updated_at
|
||
FROM user_profile up
|
||
JOIN organization_students os ON up.id = os.user_id
|
||
WHERE os.organization_id = ?
|
||
AND os.enrollment_status = 'active'
|
||
AND up.career_list IS NOT NULL
|
||
`;
|
||
params = [orgId];
|
||
|
||
const [rows] = await pool.execute(query, params);
|
||
const careerCounts = {};
|
||
|
||
for (const row of rows) {
|
||
try {
|
||
const decrypted = decrypt(row.career_list);
|
||
const careerList = JSON.parse(decrypted);
|
||
if (Array.isArray(careerList)) {
|
||
for (const career of careerList) {
|
||
const key = career.code || career.title;
|
||
if (!careerCounts[key]) {
|
||
careerCounts[key] = {
|
||
career_name: career.title,
|
||
career_soc_code: career.code,
|
||
student_count: 0
|
||
};
|
||
}
|
||
careerCounts[key].student_count++;
|
||
}
|
||
}
|
||
} catch (err) {
|
||
// Skip if decrypt/parse fails
|
||
}
|
||
}
|
||
|
||
const results = Object.values(careerCounts)
|
||
.sort((a, b) => b.student_count - a.student_count)
|
||
.slice(0, limitNum);
|
||
|
||
return res.json({ careers: results });
|
||
|
||
case 'profiled':
|
||
query = `
|
||
SELECT cp.career_name, cp.career_soc_code, COUNT(DISTINCT cp.user_id) as student_count
|
||
FROM career_profiles cp
|
||
JOIN organization_students os ON cp.user_id = os.user_id
|
||
WHERE os.organization_id = ?
|
||
AND os.enrollment_status = 'active'
|
||
${days < 9999 ? 'AND cp.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)' : ''}
|
||
GROUP BY cp.career_soc_code, cp.career_name
|
||
ORDER BY student_count DESC
|
||
LIMIT ?
|
||
`;
|
||
params = days < 9999 ? [orgId, days, limitNum] : [orgId, limitNum];
|
||
break;
|
||
|
||
default:
|
||
return res.status(400).json({ error: 'Invalid signal strength' });
|
||
}
|
||
|
||
const [results] = await pool.execute(query, params);
|
||
return res.json({ careers: results });
|
||
|
||
} catch (err) {
|
||
console.error('[career-interests] Error:', err.message);
|
||
return res.status(500).json({ error: 'Failed to load career interests' });
|
||
}
|
||
});
|
||
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
// STUDENT MANAGEMENT ENDPOINTS
|
||
// ═══════════════════════════════════════════════════════════════════════════
|
||
|
||
app.get('/api/admin/students', requireAdminAuth, async (req, res) => {
|
||
try {
|
||
const orgId = req.admin.organizationId;
|
||
const { status } = req.query;
|
||
|
||
// Return all students - filtering/search happens on frontend after decryption
|
||
let query = `
|
||
SELECT os.id, os.user_id, os.enrollment_status, os.enrollment_date,
|
||
os.invitation_sent_at, os.status_changed_date, os.bounce_reason, os.updated_at,
|
||
up.firstname, up.lastname, up.email, up.last_login, up.created_at
|
||
FROM organization_students os
|
||
JOIN user_profile up ON os.user_id = up.id
|
||
WHERE os.organization_id = ?
|
||
`;
|
||
const params = [orgId];
|
||
|
||
if (status) {
|
||
query += ` AND os.enrollment_status = ?`;
|
||
params.push(status);
|
||
}
|
||
|
||
query += ` ORDER BY os.enrollment_date DESC`;
|
||
|
||
const [students] = await pool.execute(query, params);
|
||
|
||
// Add engagement metrics for each student (with guards for missing tables)
|
||
for (const student of students) {
|
||
student.inventory_completed_at = null;
|
||
student.career_profiles_count = 0;
|
||
student.college_profiles_count = 0;
|
||
student.financial_profiles_count = 0;
|
||
student.roadmaps_count = 0;
|
||
|
||
try {
|
||
const [careerProfiles] = await pool.execute('SELECT COUNT(*) as count FROM career_profiles WHERE user_id = ?', [student.user_id]);
|
||
student.career_profiles_count = careerProfiles[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist
|
||
}
|
||
|
||
try {
|
||
const [collegeProfiles] = await pool.execute(`
|
||
SELECT COUNT(*) as count
|
||
FROM college_profiles cp_col
|
||
JOIN career_profiles cp ON cp_col.career_profile_id = cp.id
|
||
WHERE cp.user_id = ?
|
||
`, [student.user_id]);
|
||
student.college_profiles_count = collegeProfiles[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist or no join possible
|
||
}
|
||
|
||
try {
|
||
const [financialProfiles] = await pool.execute('SELECT COUNT(*) as count FROM financial_profiles WHERE user_id = ?', [student.user_id]);
|
||
student.financial_profiles_count = financialProfiles[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist
|
||
}
|
||
|
||
try {
|
||
const [milestones] = await pool.execute(`
|
||
SELECT COUNT(*) as count
|
||
FROM milestones m
|
||
JOIN career_profiles cp ON m.career_profile_id = cp.id
|
||
WHERE cp.user_id = ?
|
||
`, [student.user_id]);
|
||
student.roadmaps_count = milestones[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist or no join possible
|
||
}
|
||
}
|
||
|
||
// Decrypt all encrypted fields
|
||
for (const student of students) {
|
||
if (student.firstname) {
|
||
try {
|
||
student.firstname = decrypt(student.firstname);
|
||
} catch {
|
||
// If not encrypted or decryption fails, leave as-is
|
||
}
|
||
}
|
||
if (student.lastname) {
|
||
try {
|
||
student.lastname = decrypt(student.lastname);
|
||
} catch {
|
||
// If not encrypted or decryption fails, leave as-is
|
||
}
|
||
}
|
||
if (student.email) {
|
||
try {
|
||
student.email = decrypt(student.email);
|
||
} catch {
|
||
// If not encrypted or decryption fails, leave as-is
|
||
}
|
||
}
|
||
|
||
// SECURITY: Remove user_id before sending to frontend
|
||
delete student.user_id;
|
||
}
|
||
|
||
return res.json({ students });
|
||
} catch (err) {
|
||
console.error('[students-list] Error:', err.message);
|
||
return res.status(500).json({ error: 'Failed to load students' });
|
||
}
|
||
});
|
||
|
||
app.get('/api/admin/students/:studentId', requireAdminAuth, async (req, res) => {
|
||
try {
|
||
const orgId = req.admin.organizationId;
|
||
const { studentId } = req.params;
|
||
|
||
// studentId is now organization_students.id, not user_id
|
||
const [studentResults] = await pool.execute(`
|
||
SELECT
|
||
os.*,
|
||
up.firstname,
|
||
up.lastname,
|
||
up.email,
|
||
up.last_login,
|
||
up.created_at,
|
||
up.career_list,
|
||
up.interest_inventory_answers,
|
||
up.riasec_scores
|
||
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 (!studentResults || studentResults.length === 0) {
|
||
return res.status(404).json({ error: 'Student not found' });
|
||
}
|
||
|
||
const student = studentResults[0];
|
||
const userId = student.user_id; // Extract for internal queries
|
||
|
||
console.log('[student-detail] Raw data from DB:', {
|
||
userId,
|
||
career_list: student.career_list,
|
||
interest_inventory_answers: student.interest_inventory_answers
|
||
});
|
||
|
||
// Decrypt email
|
||
if (student.email) {
|
||
try {
|
||
student.email = decrypt(student.email);
|
||
} catch {
|
||
// Leave as-is if not encrypted
|
||
}
|
||
}
|
||
|
||
// Check if interest inventory is completed (60-char string like "111222333...")
|
||
student.inventory_completed_at = null;
|
||
if (student.interest_inventory_answers && student.interest_inventory_answers.length === 60) {
|
||
student.inventory_completed_at = student.created_at;
|
||
}
|
||
|
||
// Check if careers have been added to comparison list (career_list is JSON array in user_profile)
|
||
student.career_comparison_count = 0;
|
||
if (student.career_list) {
|
||
try {
|
||
const careerList = typeof student.career_list === 'string'
|
||
? JSON.parse(student.career_list)
|
||
: student.career_list;
|
||
|
||
student.career_comparison_count = Array.isArray(careerList) ? careerList.length : 0;
|
||
} catch {
|
||
// Invalid JSON or not an array
|
||
}
|
||
}
|
||
|
||
// Count career profiles, college profiles, financial profiles, and roadmaps (premium features)
|
||
student.career_profiles_count = 0;
|
||
student.college_profiles_count = 0;
|
||
student.financial_profiles_count = 0;
|
||
student.roadmaps_count = 0;
|
||
|
||
try {
|
||
const [careerProfiles] = await pool.execute('SELECT COUNT(*) as count FROM career_profiles WHERE user_id = ?', [userId]);
|
||
student.career_profiles_count = careerProfiles[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist
|
||
}
|
||
|
||
try {
|
||
const [collegeProfiles] = await pool.execute('SELECT COUNT(*) as count FROM college_profiles WHERE user_id = ?', [userId]);
|
||
student.college_profiles_count = collegeProfiles[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist
|
||
}
|
||
|
||
try {
|
||
const [financialProfiles] = await pool.execute('SELECT COUNT(*) as count FROM financial_profiles WHERE user_id = ?', [userId]);
|
||
student.financial_profiles_count = financialProfiles[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist
|
||
}
|
||
|
||
try {
|
||
const [milestones] = await pool.execute(`
|
||
SELECT COUNT(*) as count
|
||
FROM milestones m
|
||
JOIN career_profiles cp ON m.career_profile_id = cp.id
|
||
WHERE cp.user_id = ?
|
||
`, [userId]);
|
||
student.roadmaps_count = milestones[0].count;
|
||
} catch (err) {
|
||
// Table doesn't exist or no join possible
|
||
}
|
||
|
||
// Remove sensitive fields before continuing
|
||
delete student.career_list;
|
||
delete student.interest_inventory_answers;
|
||
|
||
const [privacyResults] = await pool.execute(`
|
||
SELECT * FROM student_privacy_settings
|
||
WHERE user_id = ? AND organization_id = ?
|
||
LIMIT 1
|
||
`, [userId, orgId]);
|
||
|
||
// Default to all false (private by default) if no settings exist
|
||
const privacy = privacyResults[0] || {
|
||
share_career_exploration: false,
|
||
share_interest_inventory: false,
|
||
share_career_profiles: false,
|
||
share_college_profiles: false,
|
||
share_financial_profile: false,
|
||
share_roadmap: false
|
||
};
|
||
|
||
let careers = [];
|
||
if (privacy.share_career_exploration) {
|
||
const [careerResults] = await pool.execute(`
|
||
SELECT career_name, career_soc_code, COUNT(*) as view_count, MAX(viewed_at) as last_viewed
|
||
FROM career_views
|
||
WHERE user_id = ?
|
||
GROUP BY career_soc_code, career_name
|
||
ORDER BY last_viewed DESC
|
||
LIMIT 10
|
||
`, [userId]);
|
||
careers = careerResults;
|
||
}
|
||
|
||
// Fetch RIASEC scores if privacy allows
|
||
let riasecScores = null;
|
||
console.log('[student-detail] RIASEC check:', {
|
||
share_interest_inventory: privacy.share_interest_inventory,
|
||
has_riasec_scores: !!student.riasec_scores,
|
||
riasec_scores_value: student.riasec_scores
|
||
});
|
||
if (privacy.share_interest_inventory && student.riasec_scores) {
|
||
try {
|
||
const decrypted = decrypt(student.riasec_scores);
|
||
const parsed = JSON.parse(decrypted);
|
||
console.log('[student-detail] RIASEC parsed:', parsed);
|
||
// Convert {"R":23,"I":25,"A":23,"S":16,"E":15,"C":22} to array format for chart
|
||
riasecScores = [
|
||
{ area: 'Realistic', score: parsed.R || 0 },
|
||
{ area: 'Investigative', score: parsed.I || 0 },
|
||
{ area: 'Artistic', score: parsed.A || 0 },
|
||
{ area: 'Social', score: parsed.S || 0 },
|
||
{ area: 'Enterprising', score: parsed.E || 0 },
|
||
{ area: 'Conventional', score: parsed.C || 0 }
|
||
];
|
||
} catch (err) {
|
||
console.error('[student-detail] RIASEC error:', err.message);
|
||
}
|
||
}
|
||
delete student.riasec_scores;
|
||
|
||
// Fetch career profiles if privacy allows
|
||
let careerProfiles = [];
|
||
if (privacy.share_career_profiles) {
|
||
try {
|
||
const [profiles] = await pool.execute(`
|
||
SELECT id, career_name, status, start_date, retirement_start_date,
|
||
college_enrollment_status, currently_working, scenario_title, created_at
|
||
FROM career_profiles
|
||
WHERE user_id = ?
|
||
ORDER BY created_at DESC
|
||
`, [userId]);
|
||
console.log('[student-detail] Career profiles found:', profiles.length);
|
||
careerProfiles = profiles;
|
||
} catch (err) {
|
||
console.error('[student-detail] Career profiles error:', err.message);
|
||
}
|
||
}
|
||
|
||
// Fetch college profiles if privacy allows
|
||
let collegeProfiles = [];
|
||
if (privacy.share_college_profiles) {
|
||
try {
|
||
const [profiles] = await pool.execute(`
|
||
SELECT id, selected_school, selected_program, program_type,
|
||
college_enrollment_status, tuition, expected_graduation, expected_salary, created_at
|
||
FROM college_profiles
|
||
WHERE user_id = ?
|
||
ORDER BY created_at DESC
|
||
`, [userId]);
|
||
console.log('[student-detail] College profiles found:', profiles.length);
|
||
collegeProfiles = profiles;
|
||
} catch (err) {
|
||
console.error('[student-detail] College profiles error:', err.message);
|
||
}
|
||
}
|
||
|
||
// Fetch career roadmap milestones if privacy allows
|
||
let roadmapMilestones = [];
|
||
if (privacy.share_roadmap) {
|
||
try {
|
||
const [milestones] = await pool.execute(`
|
||
SELECT m.id, m.title, m.description, m.date, m.status, cp.career_name
|
||
FROM milestones m
|
||
JOIN career_profiles cp ON m.career_profile_id = cp.id
|
||
WHERE cp.user_id = ?
|
||
ORDER BY m.date ASC
|
||
`, [userId]);
|
||
console.log('[student-detail] Roadmap milestones found:', milestones.length);
|
||
roadmapMilestones = milestones;
|
||
} catch (err) {
|
||
console.error('[student-detail] Roadmap milestones error:', err.message);
|
||
}
|
||
}
|
||
|
||
// SECURITY: Remove user_id before sending to frontend
|
||
delete student.user_id;
|
||
if (privacy.user_id) delete privacy.user_id;
|
||
|
||
return res.json({
|
||
student,
|
||
privacy,
|
||
careers,
|
||
riasecScores,
|
||
careerProfiles,
|
||
collegeProfiles,
|
||
roadmapMilestones
|
||
});
|
||
} catch (err) {
|
||
console.error('[student-detail] Error:', err.message);
|
||
return res.status(500).json({ error: 'Failed to load student details' });
|
||
}
|
||
});
|
||
|
||
// Reset password for student
|
||
app.post('/api/admin/students/:studentId/reset-password', requireAdminAuth, async (req, res) => {
|
||
try {
|
||
const orgId = req.admin.organizationId;
|
||
const { studentId } = req.params;
|
||
const db = pool.raw || pool;
|
||
|
||
// Verify student belongs to this org (studentId is organization_students.id)
|
||
const [studentCheck] = await pool.execute(`
|
||
SELECT os.*, up.email
|
||
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 (!studentCheck || studentCheck.length === 0) {
|
||
return res.status(404).json({ error: 'Student not found' });
|
||
}
|
||
|
||
const student = studentCheck[0];
|
||
|
||
// Decrypt email
|
||
let email = student.email;
|
||
try {
|
||
email = decrypt(email);
|
||
} catch (err) {
|
||
// Email might not be encrypted
|
||
}
|
||
|
||
const emailNorm = String(email).trim().toLowerCase();
|
||
|
||
// Generate reset token
|
||
const token = crypto.randomBytes(32).toString('hex');
|
||
const tokenHash = crypto.createHash('sha256').update(token).digest('hex');
|
||
const now = Date.now();
|
||
const RESET_TTL_MIN = 60;
|
||
const expiresAt = now + RESET_TTL_MIN * 60 * 1000;
|
||
|
||
// Store reset token in password_resets table
|
||
await db.query(
|
||
`INSERT INTO password_resets (email, token_hash, expires_at, created_at, ip)
|
||
VALUES (?, ?, ?, ?, ?)`,
|
||
[emailNorm, tokenHash, expiresAt, now, req.ip || 'admin-portal']
|
||
);
|
||
|
||
// Build reset link (using student portal URL)
|
||
const baseUrl = (process.env.APTIVA_API_BASE || '').replace(/\/+$/, '');
|
||
const resetLink = `${baseUrl}/reset-password/${token}`;
|
||
|
||
const emailText =
|
||
`Your administrator has requested a password reset for your Aptiva account.
|
||
|
||
Use the link below to reset your password (valid for ${RESET_TTL_MIN} minutes):
|
||
${resetLink}
|
||
|
||
If you didn't request this, you can ignore this email.`;
|
||
|
||
// Send email via SendGrid or log in dev mode
|
||
if (SENDGRID_KEY) {
|
||
await sgMail.send({
|
||
to: emailNorm,
|
||
from: 'no-reply@aptivaai.com',
|
||
subject: 'Reset your Aptiva password',
|
||
text: emailText,
|
||
html: `<pre style="font-family: ui-monospace, Menlo, monospace; white-space: pre-wrap">${emailText}</pre>`
|
||
});
|
||
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}`);
|
||
});
|