--- package.json ---
{
"name": "myconsultation",
"version": "1.0.0",
"scripts": {
"server": "node server.mjs",
"migrations": "node common/setup.mjs",
"start:staging": "NODE_ENV=staging node server.mjs",
"reset": "node common/setup.mjs --force-reset",
"encrypt-value": "node common/encrypt-value.mjs",
"encrypt-key": "node common/encrypt-value.mjs --default"
},
"dependencies": {
"bcrypt": "^6.0.0",
"cookie-parser": "^1.4.7",
"cors": "^2.8.5",
"crypto": "^1.0.1",
"dotenv": "^16.5.0",
"express": "^5.1.0",
"express-rate-limit": "^8.5.2",
"helmet": "^8.2.0",
"jsonwebtoken": "^9.0.2",
"mysql2": "^3.22.3",
"nodemailer": "^7.0.3",
"sanitize-html": "^2.17.0"
}
}
--- .gitignore ---
node_modules
node/.env
*.zip
app*.log
logs
schema.json
tmp/
node/tmp/
error_log
package-lock.json
node/.env.staging
stderr.log
.env
.env.production
node/.env.production
proxy_server.mjs
root_startup.log
server_debug.log
node/package-lock.json
npm-debug.log
code-analysis
--- server.mjs ---
import dotenv from 'dotenv';
import fs from 'fs';
import express from 'express';
import path from 'path';
import { fileURLToPath } from 'url';
import nodemailer from 'nodemailer';
import cors from 'cors';
import cookieParser from 'cookie-parser';
import helmet from 'helmet';
import rateLimit from 'express-rate-limit';
import { setupAuthRoutes } from './authRoutes.mjs';
import { setupAdminRoutes } from './adminRoutes.mjs';
import { setupClientRoutes } from './clientRoutes.mjs';
import { setupDbSetupRoute, startDatabase } from './common/database.mjs';
import { decrypt, getDefaultKey } from './common/decrypt.mjs';
import { setupHealthRoute, writeLog } from './common/utils.mjs';
import { setupStaffRoutes } from './staffRoutes.mjs';
import { cleanupOldLogs } from './common/cleanup_old_logs.mjs';
import { runCleanup } from './scripts/inactive_user_cleanup.mjs';
import { executeSql } from './common/database.mjs';
const __filename_log = fileURLToPath(import.meta.url);
const __dirname_log = path.dirname(__filename_log);
const rootLogPath = path.join(__dirname_log, 'root_startup.log');
function rootLog(msg) { try { fs.appendFileSync(rootLogPath, `[${new Date().toISOString()}] node/server.mjs: ${msg}\n`); } catch(e) {} }
rootLog("Module loaded, starting execution");
process.on('uncaughtException', (err) => {
rootLog(`Uncaught Exception: ${err.stack || err}`);
process.exit(1);
});
process.on('unhandledRejection', (reason, promise) => {
rootLog(`Unhandled Rejection: ${reason.stack || reason}`);
});
writeLog("--- SERVER.JS EXECUTION STARTED ---");
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
// Load environment variables from .env file, or a specific file like .env.staging
let envPath = path.join(__dirname, '.env.production');
if (!fs.existsSync(envPath)) {
envPath = path.join(__dirname, '.env');
}
if (fs.existsSync(envPath)) {
const envConfig = dotenv.parse(fs.readFileSync(envPath));
for (const k in envConfig) {
process.env[k] = envConfig[k];
}
}
let port = process.env.PORT || 3020;
let appContext = process.env.APP_CONTEXT || '/';
let appProtocol = process.env.APP_PROTOCOL || 'http';
let appHost = process.env.APP_HOST || `127.0.0.1:${port}`;
let appUrl = `${appProtocol}://${appHost}${appContext}`;
writeLog('appUrl: ', appUrl, ', appContext: ', appContext);
const app = express();
// Redirect HTTP traffic to HTTPS in production environments
if (process.env.NODE_ENV === 'production') {
app.use((req, res, next) => {
if (req.header('x-forwarded-proto') !== 'https') {
return res.redirect(`https://${req.header('host')}${req.url}`);
}
next();
});
}
app.use(helmet({
contentSecurityPolicy: {
directives: {
defaultSrc: ["'self'"],
scriptSrc: ["'self'", "'unsafe-inline'", "https://cdn.ckeditor.com"],
styleSrc: ["'self'", "'unsafe-inline'"],
imgSrc: ["'self'", "data:"],
connectSrc: ["'self'"],
fontSrc: ["'self'"],
objectSrc: ["'none'"],
mediaSrc: ["'self'"],
frameSrc: ["'none'"],
},
},
})); // Set security HTTP headers, including customized Content Security Policy (CSP)
app.use(cors()); // Enable Cross-Origin Resource Sharing
app.use(express.json()); // Middleware for parsing JSON bodies
app.use(cookieParser()); // Middleware for parsing cookies
// Serve static files (HTML, CSS, frontend JS) from a 'public' directory
// Create a folder named 'public' in your project root and put register.html and style.css there.
app.use(express.static(path.join(__dirname, 'public')));
const router = express.Router();
// Set up rate limiter: maximum of 5 requests per 15 minutes
const loginLimiter = rateLimit({
windowMs: 15 * 60 * 1000,
max: 5,
message: { error: 'Too many attempts from this IP, please try again after 15 minutes' }
});
// Apply rate limiter to login and password reset endpoints
app.use([
'/api/login',
'/api/client/login-otp/send',
'/api/client/login-otp/verify',
'/api/forgot-password',
'/api/reset-password'
], loginLimiter);
// Redirect root to login page
router.get('/', (req, res) => {
res.redirect('/login.html');
});
setupHealthRoute(router);
// Get encryption key from a secure env var (not in .env file)
const ENC_KEY = process.env.ENC_KEY || getDefaultKey();
// Database connection
const db = await startDatabase({
host: process.env.DB_HOST || 'localhost', // Or your MySQL host
user: process.env.DB_USER, // Your MySQL username
password: process.env.DB_PASSWORD, // Your MySQL password from environment variable
db: process.env.DB_DATABASE, // Your database name
encryptionKey: ENC_KEY
});
setupRoutes(router, db, ENC_KEY);
app.use('/', router);
app.listen(port, () => {
writeLog('Server listening on ' + appUrl, process.env.PORT);
// Schedule log cleanup to run every 24 hours
setInterval(() => {
writeLog('[Scheduler] Running daily log cleanup...');
cleanupOldLogs();
writeLog('[Scheduler] Running daily inactive user cleanup...');
runCleanup();
}, 24 * 60 * 60 * 1000); // 24 hours
writeLog('[Scheduler] Daily log cleanup scheduled.');
});
function setupRoutes(router, db, ENC_KEY) {
const JWT_SECRET = process.env.JWT_SECRET;
const EMAIL_USER = process.env.EMAIL_USER || ''; // Your email for sending
const EMAIL_PASS = process.env.EMAIL_PASS;
writeLog(' Email User:', EMAIL_USER);
// Email transporter setup
const transporter = nodemailer.createTransport({
service: 'Gmail', // Or your email provider
auth: {
user: EMAIL_USER,
pass: EMAIL_PASS,
},
});
setupDbSetupRoute(router, db);
setupAuthRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl });
setupAdminRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl });
setupClientRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl });
setupStaffRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl });
}
--- adminRoutes.mjs ---
import bcrypt from 'bcrypt';
import jwt from 'jsonwebtoken';
import { writeLog, checkPasswordPolicy } from './common/utils.mjs';
import sanitizeHtml from 'sanitize-html';
import { executeSql } from './common/database.mjs';
import { exec } from 'child_process';
import path from 'path';
export function setupAdminRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl }) {
// Middleware to verify Admin JWT
const verifyAdminToken = (req, res, next) => {
const token = req.cookies?.token;
if (token) {
jwt.verify(token, JWT_SECRET, (err, decoded) => {
if (err) {
writeLog('[verifyAdminToken] JWT verification error:', err.name, err.message, 'ExpiredAt:', err.expiredAt); // Log specific JWT error
return res.status(403).json({ error: `Forbidden: Token verification failed (${err.name})` });
}
let userRoles = [];
if (decoded.roles && Array.isArray(decoded.roles)) {
userRoles = decoded.roles;
} else if (typeof decoded.role === 'string') {
userRoles = [decoded.role];
}
if (userRoles.length === 0) {
return res.status(403).json({ error: 'Forbidden: No roles found in token' });
}
const hasAdmnRole = userRoles.includes('admin');
if (!hasAdmnRole) {
return res.status(403).json({ error: 'Forbidden: Invalid or missing admin token' });
}
req.user = { ...decoded, roles: userRoles };
next();
});
} else {
writeLog('[verifyAdminToken] Unauthorized: Missing token in cookies or headers.');
res.status(401).json({ error: 'Unauthorized: Missing token' });
}
};
const verifyNutritionistToken = (req, res, next) => {
const token = req.cookies?.token;
if (token) {
jwt.verify(token, JWT_SECRET, (err, decoded) => {
if (err) {
writeLog(`[verifyNutritionistToken] JWT verification error: ${err.name}`);
return res.status(403).json({ error: `Forbidden: Token verification failed (${err.name})` });
}
let userRoles = [];
if (decoded.roles && Array.isArray(decoded.roles)) {
userRoles = decoded.roles;
} else if (typeof decoded.role === 'string') {
userRoles = [decoded.role];
}
if (!userRoles.includes('nutritionist')) {
return res.status(403).json({ error: 'Forbidden: Access denied. Not a nutritionist.' });
}
req.user = { ...decoded, userId: decoded.userId, roles: userRoles };
next();
});
} else {
writeLog('[verifyNutritionistToken] Unauthorized: Missing token in cookies or headers.');
res.status(401).json({ error: 'Unauthorized: Missing token' });
}
};
const verifyExecutiveToken = (req, res, next) => {
const token = req.cookies?.token;
if (token) {
jwt.verify(token, JWT_SECRET, (err, decoded) => {
if (err) {
writeLog(`[verifyExecutiveToken] JWT verification error: ${err.name}`);
return res.status(403).json({ error: `Forbidden: Token verification failed (${err.name})` });
}
let userRoles = [];
if (decoded.roles && Array.isArray(decoded.roles)) {
userRoles = decoded.roles;
} else if (typeof decoded.role === 'string') {
userRoles = [decoded.role];
}
if (!userRoles.includes('executive')) {
return res.status(403).json({ error: 'Forbidden: Access denied. Not an executive.' });
}
req.user = { ...decoded, userId: decoded.userId, roles: userRoles };
next();
});
} else {
writeLog('[verifyExecutiveToken] Unauthorized: Missing token in cookies or headers.');
res.status(401).json({ error: 'Unauthorized: Missing token' });
}
};
// --- Initial Admin Setup ---
router.post('/api/setup/first-admin', async (req, res) => {
const { first_name, last_name, email, password } = req.body;
if (!first_name || !last_name || !email || !password) {
return res.status(400).json({ error: 'All fields are required' });
}
if (password.length < 6) {
return res.status(400).json({ error: 'Password must be at least 6 characters long' });
}
const passwordPolicyResult = checkPasswordPolicy(password);
if (!passwordPolicyResult.isValid) {
return res.status(400).json({ error: passwordPolicyResult.message });
}
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (!emailRegex.test(email)) {
return res.status(400).json({ error: 'Invalid email format' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Check if an admin already exists
const [adminUsers] = await executeSql(connection, `
SELECT ur.user_id FROM user_roles ur
JOIN roles r ON ur.role_id = r.role_id
WHERE r.role_name = 'admin' LIMIT 1
`);
if (adminUsers.length > 0) {
await connection.rollback();
return res.status(403).json({ error: 'Forbidden: An admin account already exists. Setup is complete.' });
}
// Check if the email is already in use
const [existingEmail] = await executeSql(connection, "SELECT user_id FROM users_v2 WHERE email = ?", [email]);
if (existingEmail.length > 0) {
await connection.rollback();
return res.status(409).json({ error: 'Email already in use.' });
}
const hashedPassword = await bcrypt.hash(password, 10);
// Insert into the new users_v2 table. is_email_verified is true for admin by default.
const [newUserResult] = await executeSql(connection,
'INSERT INTO users_v2 (first_name, last_name, email, password_hash, is_active, is_email_verified) VALUES (?, ?, ?, ?, ?, ?)',
[first_name, last_name, email, hashedPassword, true, true]
);
const newUserId = newUserResult.insertId;
// Link user to 'admin' role
const [adminRole] = await executeSql(connection, "SELECT role_id FROM roles WHERE role_name = 'admin'");
if (adminRole.length === 0) throw new Error("Critical: 'admin' role not found in roles table.");
await executeSql(connection, 'INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)', [newUserId, adminRole[0].role_id]);
await connection.commit();
res.status(201).json({ message: 'First admin account created successfully!', user_id: newUserId });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error creating first admin:', error);
res.status(500).json({ error: 'Failed to create first admin' });
} finally {
if (connection) connection.release();
}
});
// --- Admin Authentication ---
router.get('/admin.html', verifyAdminToken, (req, res) => {
res.sendFile(__dirname + '/public/admin.html');
});
// --- Admin Routes ---
// Get all staff (nutritionists and executives)
router.get('/api/admin/staff', verifyAdminToken, async (req, res) => {
try {
const [staff] = await executeSql(db, `
SELECT
u.user_id,
u.first_name,
u.last_name,
u.email,
u.is_active,
GROUP_CONCAT(DISTINCT r.role_name) as roles,
(SELECT GROUP_CONCAT(CONCAT(c.first_name, ' ', c.last_name, ' (ID: ', c.user_id, ')') SEPARATOR '|')
FROM users_v2 c
WHERE (c.assigned_nutritionist_id = u.user_id OR c.assigned_executive_id = u.user_id) AND c.deleted = 0
) as assigned_clients
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
GROUP BY u.user_id, u.first_name, u.last_name, u.email, u.is_active
HAVING SUM(CASE WHEN r.role_name = 'client' THEN 0 ELSE 1 END) > 0
ORDER BY u.user_id + 0
`);
const staffWithRoles = staff.map(user => {
return {
...user,
roles: user.roles ? user.roles.split(',') : [],
assigned_clients: user.assigned_clients ? user.assigned_clients.split('|') : []
};
});
res.json(staffWithRoles);
} catch (error) {
writeLog('Error fetching staff:', error);
res.status(500).json({ error: 'Failed to fetch staff' });
}
});
// Add a new nutritionist
// Add new staff
router.post('/api/admin/staff', verifyAdminToken, async (req, res) => {
const { first_name, last_name, email, password, roles } = req.body; // Expecting roles as ['N', 'E']
if (!first_name || !last_name || !email || !password || !Array.isArray(roles) || roles.length === 0) {
return res.status(400).json({ error: 'All fields and at least one role are required' });
}
const passwordPolicyResult = checkPasswordPolicy(password);
if (!passwordPolicyResult.isValid) {
return res.status(400).json({ error: passwordPolicyResult.message });
}
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (!emailRegex.test(email)) {
return res.status(400).json({ error: 'Invalid email format' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
const [existingUser] = await executeSql(connection, 'SELECT user_id FROM users_v2 WHERE email = ?', [email]);
if (existingUser.length > 0) {
await connection.rollback();
return res.status(409).json({ error: 'This email is already in use by another user.' });
}
const hashedPassword = await bcrypt.hash(password, 10);
const [newUserResult] = await executeSql(connection,
'INSERT INTO users_v2 (first_name, last_name, email, password_hash, is_active, is_email_verified) VALUES (?, ?, ?, ?, ?, ?)',
[first_name, last_name, email, hashedPassword, true, true] // Staff are auto-verified
);
const userId = newUserResult.insertId;
const message = 'New staff member added successfully.';
const roleMap = { 'A': 'admin', 'N': 'nutritionist', 'E': 'executive' };
const roleNames = roles.map(code => roleMap[code]).filter(Boolean);
if (roleNames.length !== roles.length) {
await connection.rollback();
return res.status(400).json({ error: 'Invalid role code provided.' });
}
const placeholders = roleNames.map(() => '?').join(',');
const [roleRows] = await executeSql(connection, `SELECT role_id FROM roles WHERE role_name IN (${placeholders})`, roleNames);
if (roleRows.length !== roleNames.length) {
await connection.rollback();
return res.status(500).json({ error: 'Could not find all specified roles in the database.' });
}
const userRolesData = roleRows.map(row => [userId, row.role_id]);
if (userRolesData.length > 0) {
// Insert the roles for the new staff member.
await connection.query('INSERT INTO user_roles (user_id, role_id) VALUES ?', [userRolesData]);
}
await connection.commit();
res.status(201).json({ message: message, user_id: userId });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error adding staff:', error);
res.status(500).json({ error: 'Failed to add staff' });
} finally {
if (connection) connection.release();
}
});
// Update user roles
router.patch('/api/admin/users/:userId/roles', verifyAdminToken, async (req, res) => {
const { userId } = req.params;
const { roles } = req.body; // Expecting an array of role codes, e.g., ['N', 'E']
if (!Array.isArray(roles)) {
return res.status(400).json({ error: 'Roles must be an array' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Delete existing non-client roles from `user_roles` for this user
await executeSql(connection, 'DELETE FROM user_roles WHERE user_id = ? AND role_id != (SELECT role_id FROM roles WHERE role_name = "client")', [userId]);
// Insert new roles into `user_roles`
if (roles.length > 0) {
const roleMap = { 'A': 'admin', 'N': 'nutritionist', 'E': 'executive' };
const roleNames = roles.map(code => roleMap[code]).filter(Boolean);
if (roleNames.length !== roles.length) {
await connection.rollback();
return res.status(400).json({ error: 'Invalid role code provided.' });
}
const placeholders = roleNames.map(() => '?').join(',');
const [roleRows] = await executeSql(connection, `SELECT role_id FROM roles WHERE role_name IN (${placeholders})`, roleNames);
if (roleRows.length !== roleNames.length) {
await connection.rollback();
return res.status(500).json({ error: 'Could not find all specified roles in the database.' });
}
const userRolesData = roleRows.map(row => [userId, row.role_id]);
await connection.query('INSERT INTO user_roles (user_id, role_id) VALUES ?', [userRolesData]);
}
await connection.commit();
res.json({ message: 'User roles updated successfully' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error updating user roles:', error);
res.status(500).json({ error: 'Failed to update user roles' });
} finally {
if (connection) connection.release();
}
});
// Activate/Deactivate staff user
router.patch('/api/admin/users/:userId/status', verifyAdminToken, async (req, res) => {
const { userId } = req.params;
const { is_active } = req.body;
if (typeof is_active !== 'boolean') {
return res.status(400).json({ error: 'is_active field must be a boolean' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Update new table
const [result] = await executeSql(connection, 'UPDATE users_v2 SET is_active = ? WHERE user_id = ?', [is_active, userId]);
if (result.affectedRows === 0) {
await connection.rollback();
return res.status(404).json({ error: 'User not found' });
}
await connection.commit();
res.json({ message: `User status updated successfully` });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error updating user status:', error);
res.status(500).json({ error: 'Failed to update user status' });
} finally {
if (connection) connection.release();
}
});
// Admin: Update staff details
router.put('/api/admin/staff/:userId', verifyAdminToken, async (req, res) => {
const { userId } = req.params;
const { first_name, last_name, email } = req.body;
if (!first_name || !last_name || !email) {
return res.status(400).json({ error: 'First name, last name, and email are required.' });
}
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (!emailRegex.test(email)) {
return res.status(400).json({ error: 'Invalid email format.' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Check if the new email already exists for another user
const [existingUser] = await executeSql(connection,
'SELECT user_id FROM users_v2 WHERE email = ? AND user_id != ?',
[email, userId]
);
if (existingUser.length > 0) {
await connection.rollback();
return res.status(409).json({ error: 'This email address is already in use by another user.' });
}
const [result] = await executeSql(connection,
'UPDATE users_v2 SET first_name = ?, last_name = ?, email = ? WHERE user_id = ?',
[first_name, last_name, email, userId]
);
if (result.affectedRows === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Staff member not found.' });
}
await connection.commit();
res.json({ message: 'Staff details updated successfully.' });
} catch (error) {
if (connection) await connection.rollback();
writeLog(`Error updating staff details for user ${userId}:`, error);
res.status(500).json({ error: 'Failed to update staff details.' });
} finally {
if (connection) connection.release();
}
});
// Route to serve admin.html, protected by admin token
router.get('/api/admin/clients', verifyAdminToken, async (req, res) => {
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number,
u.created_at as registration_date,
u.is_email_verified,
u.last_login,
u.is_active as is_account_active,
u.assigned_nutritionist_id as nutritionist_id,
CONCAT(nutri.first_name, ' ', nutri.last_name) as nutritionist_name,
cc.is_finalized,
cc.is_food_plan_complete,
(SELECT COUNT(*) > 0 FROM client_food_plans cfp WHERE cfp.client_consultation_id = cc.client_consultation_id) as has_food_plan_suggested,
u.assigned_executive_id as enrolled_by_executive_id,
CONCAT(exec.first_name, ' ', exec.last_name) as executive_name
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id AND ur.role_id = (SELECT role_id FROM roles WHERE role_name = 'client')
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
LEFT JOIN users_v2 nutri ON u.assigned_nutritionist_id = nutri.user_id
LEFT JOIN users_v2 exec ON u.assigned_executive_id = exec.user_id
WHERE u.deleted = 0
ORDER BY u.user_id DESC
`);
res.json(clients);
} catch (error) {
writeLog('Error fetching all clients for admin:', error);
res.status(500).json({ error: 'Failed to fetch clients' });
}
});
// Toggle client account active status by Admin
router.patch('/api/admin/clients/:clientId/status', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const { is_active } = req.body; // Expecting { is_active: true/false }
if (typeof is_active !== 'boolean') {
return res.status(400).json({ error: 'is_active field must be a boolean' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
const [result] = await executeSql(connection,
'UPDATE users_v2 SET is_active = ? WHERE user_id = ?',
[is_active, clientId]
);
if (result.affectedRows === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Client not found' });
}
// If account was activated, send an email to the client
if (is_active) {
const [users] = await executeSql(connection,
'SELECT email, first_name FROM users_v2 WHERE user_id = ?',
[clientId]);
if (users.length > 0) {
const client = users[0];
const mailOptions = {
from: `"Consultation Service" <${EMAIL_USER}>`,
to: client.email,
subject: 'Your Account Has Been Activated!',
html: `
Dear ${client.first_name},
Great news! Your account with Consultation Service has been activated by our admin team.
You can now log in using your registered email address and the password you created during registration.
If you have any questions or need assistance, please feel free to contact us.
Welcome aboard!
Sincerely,
The Consultation Service Team
`,
};
if (EMAIL_USER !== '') {
try {
await transporter.sendMail(mailOptions);
writeLog(`Sent account activation email to ${client.email}`);
} catch (emailError) {
writeLog('Error sending activation email:', emailError);
}
} else {
writeLog(`Could not send account activation email to ${client.email} due to missing email setup.`);
}
}
}
await connection.commit();
res.json({ message: `Client account ${is_active ? 'activated' : 'deactivated'} successfully` });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error updating client account status:', error);
res.status(500).json({ error: 'Failed to update client account status' });
} finally {
if (connection) connection.release();
}
});
// Admin: Assign Nutritionist to Client
router.patch('/api/admin/clients/:clientId/assign-nutritionist', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const { staff_id } = req.body; // staff_id here is the nutritionist's user_id
if (!staff_id) {
return res.status(400).json({ error: 'Nutritionist ID (staff_id) is required.' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Optional: Verify staff_id is a valid active nutritionist
const [nutritionistUser] = await executeSql(connection,
`SELECT u.user_id FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
WHERE u.user_id = ? AND r.role_name = 'nutritionist' AND u.is_active = TRUE`,
[staff_id]
);
if (nutritionistUser.length === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Active nutritionist not found with the provided ID.' });
}
const [result] = await executeSql(connection,
`UPDATE users_v2 SET assigned_nutritionist_id = ? WHERE user_id = ?`,
[staff_id, clientId]
);
// If the main client record wasn't updated, it's an error.
if (result.affectedRows === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Client not found or no change made.' });
}
await connection.commit();
res.json({ message: 'Nutritionist assigned successfully to client.' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error assigning nutritionist to client:', error);
res.status(500).json({ error: 'Failed to assign nutritionist.' });
} finally {
if (connection) connection.release();
}
});
// Admin: Assign Executive to Client
router.patch('/api/admin/clients/:clientId/assign-executive', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const { staff_id } = req.body; // staff_id here is the executive's user_id
if (!staff_id) {
return res.status(400).json({ error: 'Executive ID (staff_id) is required.' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Verify staff_id is a valid active executive from the new tables
const [executiveUser] = await executeSql(connection,
`SELECT u.user_id FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
WHERE u.user_id = ? AND r.role_name = 'executive' AND u.is_active = TRUE`,
[staff_id]
);
if (executiveUser.length === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Active executive not found with the provided ID.' });
}
const [result] = await executeSql(connection,
`UPDATE users_v2 SET assigned_executive_id = ? WHERE user_id = ?`,
[staff_id, clientId]
);
if (result.affectedRows === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Client not found or no change made.' });
}
await connection.commit();
res.json({ message: 'Executive linked successfully to client.' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error linking executive to client:', error);
res.status(500).json({ error: 'Failed to link executive.' });
} finally {
if (connection) connection.release();
}
});
// Admin: Get specific client details
router.get('/api/admin/clients/:clientId/details', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number
FROM users_v2 u
WHERE u.user_id = ?`,
[clientId]
);
if (clients.length === 0) {
return res.status(404).json({ error: 'Client not found' });
}
res.json(clients[0]);
} catch (error) {
writeLog('Error fetching client details for admin:', error);
res.status(500).json({ error: 'Failed to fetch client details' });
}
});
// Admin: Update specific client details
router.put('/api/admin/clients/:clientId/details', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const { first_name, last_name, email, mobile_number } = req.body;
if (!first_name || !last_name || !email || !mobile_number) {
return res.status(400).json({ error: 'All fields (first_name, last_name, email, mobile_number) are required' });
}
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (!emailRegex.test(email)) {
return res.status(400).json({ error: 'Invalid email format' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Check if the new email already exists for another user in the new system
const [existingV2Users] = await executeSql(connection,
`SELECT user_id FROM users_v2 WHERE email = ? AND user_id != ?`,
[email, clientId]
);
if (existingV2Users.length > 0) {
await connection.rollback();
return res.status(409).json({ error: 'Email already registered for another user.' });
}
// Update new `users_v2` table
const [result] = await executeSql(connection,
`UPDATE users_v2 SET first_name = ?, last_name = ?, email = ?, mobile_number = ? WHERE user_id = ?`,
[first_name, last_name, email, mobile_number, clientId]
);
if (result.affectedRows === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Client not found or no changes made' });
}
await connection.commit();
res.json({ message: 'Client details updated successfully' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error updating client details by admin:', error);
res.status(500).json({ error: 'Failed to update client details' });
} finally {
if (connection) connection.release();
}
});
router.get('/api/admin/clients/:clientId/consultations', verifyAdminToken, async (req, res) => {
writeLog(`[${new Date().toISOString()}] /api/admin/clients/${req.params.clientId}/consultations HIT`);
try {
const [consultations] = await executeSql(db, `
SELECT * FROM client_consultations
WHERE user_id = (SELECT new_user_id FROM client_id_to_user_id_mapping WHERE old_client_id = ?)
ORDER BY created_at DESC`,
[req.params.clientId]
);
res.json(consultations);
} catch (error) {
writeLog('Error fetching client consultations for admin:', error);
res.status(500).json({ error: 'Failed to fetch client consultations' });
}
});
router.post('/api/admin/clients/:clientId/consultations', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
writeLog(`[${new Date().toISOString()}] POST /api/admin/clients/${clientId}/consultations HIT`);
const connection = await db.getConnection();
try {
await connection.beginTransaction();
const userId = clientId;
// 2. Find the latest finalized consultation for this user
const [latestConsultations] = await executeSql(connection,
'SELECT * FROM client_consultations WHERE user_id = ? AND is_finalized = TRUE ORDER BY created_at DESC LIMIT 1',
[userId]
);
if (latestConsultations.length === 0) {
await connection.rollback();
return res.status(400).json({ error: 'Cannot create a follow-up consultation until the previous one is finalized by the client.' });
}
const latestConsultation = latestConsultations[0];
// 3. Set all previous consultations for this user to is_latest = FALSE
await executeSql(connection,
'UPDATE client_consultations SET is_latest = FALSE WHERE user_id = ?',
[userId]
);
// 4. Create the new consultation by copying data from the latest one
const fieldsToCopy = [
'user_id', 'gender', 'marital_status', 'height_cms', 'weight_kg', 'age_years',
'shift_duty', 'joint_family', 'is_vegetarian', 'is_vegan', 'is_jain', 'has_lactose_intolerance',
'date_of_payment', 'health_issues', 'food_liking', 'food_disliking', 'job_description', 'job_timings',
'sedentary_status', 'travelling_frequency'
];
const [newConsultationResult] = await executeSql(connection, `
INSERT INTO client_consultations (is_latest, ${fieldsToCopy.join(', ')})
SELECT TRUE, ${fieldsToCopy.join(', ')}
FROM client_consultations
WHERE client_consultation_id = ?`,
[latestConsultation.client_consultation_id]
);
const newConsultationId = newConsultationResult.insertId;
// 5. Copy the medical history from the latest consultation to the new one
const [latestHistory] = await executeSql(connection,
'SELECT * FROM client_medical_history WHERE client_consultation_id = ? ORDER BY created_at DESC LIMIT 1',
[latestConsultation.client_consultation_id]
);
if (latestHistory.length > 0) {
const [newHistoryResult] = await executeSql(connection, `
INSERT INTO client_medical_history (client_consultation_id, family_medical_history)
VALUES (?, ?)`,
[newConsultationId, latestHistory[0].family_medical_history]
);
const newHistoryId = newHistoryResult.insertId;
// 6. Copy medications from the latest history to the new one
const [medications] = await executeSql(connection,
'SELECT * FROM client_medications WHERE history_id = ?',
[latestHistory[0].history_id]
);
if (medications.length > 0) {
const medicationValues = medications.map(med =>
[newHistoryId, med.diagnosis, med.medicine_name, med.power, med.timing, med.since_when]
);
await connection.query(
'INSERT INTO client_medications (history_id, diagnosis, medicine_name, power, timing, since_when) VALUES ?',
[medicationValues]
);
}
}
await connection.commit();
res.json({ message: 'New follow-up consultation created successfully. Client forms have been re-opened.' });
} catch (error) {
if (connection) { await connection.rollback(); }
writeLog('Error creating new consultation:', error);
res.status(500).json({ error: 'Failed to create new consultation.' });
} finally {
if (connection) connection.release();
}
});
// Admin: Search Clients
router.get('/api/admin/clients/search', verifyAdminToken, async (req, res) => {
writeLog(`[${new Date().toISOString()}] /api/admin/clients/search HIT with query:`, req.query);
try {
let baseQuery = `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number,
u.created_at as registration_date,
u.is_email_verified,
u.last_login,
u.is_active as is_account_active,
u.assigned_nutritionist_id as nutritionist_id,
CONCAT(nutri.first_name, ' ', nutri.last_name) as nutritionist_name,
cc.is_finalized,
cc.is_food_plan_complete,
(SELECT COUNT(*) > 0 FROM client_food_plans cfp WHERE cfp.client_consultation_id = cc.client_consultation_id) as has_food_plan_suggested,
u.assigned_executive_id as enrolled_by_executive_id,
CONCAT(exec.first_name, ' ', exec.last_name) as executive_name
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id AND ur.role_id = (SELECT role_id FROM roles WHERE role_name = 'client')
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
LEFT JOIN users_v2 nutri ON u.assigned_nutritionist_id = nutri.user_id
LEFT JOIN users_v2 exec ON u.assigned_executive_id = exec.user_id
`;
const conditions = ["u.deleted = 0"];
const params = [];
if (req.query.client_id) {
conditions.push("u.user_id = ?");
params.push(req.query.client_id);
}
if (req.query.first_name) {
conditions.push("u.first_name LIKE ?");
params.push(`%${req.query.first_name}%`);
}
if (req.query.last_name) {
conditions.push("u.last_name LIKE ?");
params.push(`%${req.query.last_name}%`);
}
if (req.query.email) {
conditions.push("u.email LIKE ?");
params.push(`%${req.query.email}%`);
}
if (conditions.length > 0) {
baseQuery += " WHERE " + conditions.join(" AND ");
}
baseQuery += " ORDER BY u.created_at DESC";
const [clients] = await executeSql(db, baseQuery, params);
res.json(clients);
} catch (error) {
writeLog('Error searching clients for admin:', error);
res.status(500).json({ error: 'Failed to search clients' });
}
});
// --- Client Staff Selection API Routes ---
// API to list active nutritionists for client selection
router.post('/api/admin/general-food-recommendations', verifyAdminToken, async (req, res) => {
const { recommendations_text } = req.body;
const adminUserId = req.user.userId; // From verifyAdminToken
writeLog(`[${new Date().toISOString()}] ADMIN /api/admin/general-food-recommendations HIT by admin_id: ${adminUserId}`);
if (typeof recommendations_text === 'undefined') { // Allow empty string, but not missing field
return res.status(400).json({ error: 'recommendations_text field is required.' });
}
try {
// Simple approach: Delete existing and insert new, or update if one exists.
// For simplicity, let's assume we update row with id=1, or insert if it doesn't exist.
// A more robust way might be to always update the single row or create if not present.
await executeSql(db,
'INSERT INTO general_food_recommendations (id, recommendations_text, last_updated_by) VALUES (1, ?, ?) ON DUPLICATE KEY UPDATE recommendations_text = VALUES(recommendations_text), last_updated_by = VALUES(last_updated_by)',
[recommendations_text, adminUserId]
);
res.json({ message: 'General food recommendations updated successfully.' });
} catch (error) {
writeLog('Error updating general food recommendations by admin:', error);
res.status(500).json({ error: 'Failed to update general food recommendations.' });
}
});
// API endpoint for client to save/update their medical history
router.get('/api/admin/clients/:clientId/food-plan/latest', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
writeLog(`[${new Date().toISOString()}] ADMIN /api/admin/clients/${clientId}/food-plan/latest HIT`);
try {
// 1. Get the latest plan_id for the client
const [latestPlanMeta] = await executeSql(db, `
SELECT cfp.plan_id, cfp.additional_personal_recommendations, cfp.created_at, cfp.updated_at
FROM client_food_plans cfp
JOIN client_consultations cc ON cfp.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ? AND cc.is_latest = 1
ORDER BY cfp.updated_at DESC
LIMIT 1`,
[clientId]
);
if (latestPlanMeta.length === 0) {
return res.json({ message: 'No food plan found for this client.' });
}
const plan = latestPlanMeta[0];
const planId = plan.plan_id;
// 2. Get all hourly details for that plan_id
const [hourlyDetails] = await executeSql(db,
`SELECT *
FROM client_food_plan_hourly_details
WHERE plan_id = ?
ORDER BY time_slot ASC`, // Ensure consistent order
[planId]
);
res.json({
...plan,
hourly_details: hourlyDetails
});
} catch (error) {
writeLog(`Error fetching latest food plan for client ${clientId} by admin:`, error);
res.status(500).json({ error: 'Failed to fetch latest food plan for client.' });
}
});
// Admin: Save/Update food plan for a specific client
router.post('/api/admin/clients/:clientId/food-plan', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const adminUserId = req.user.userId; // Admin who is making the change
const { hourly_plan } = req.body;
// Sanitize the HTML content to prevent XSS attacks
const sanitizedRecommendations = sanitizeHtml(req.body.additional_personal_recommendations || '', {
allowedTags: [ 'p', 'b', 'i', 'em', 'strong', 'ul', 'ol', 'li', 'br', 'h1', 'h2', 'h3' ],
allowedAttributes: {} // No attributes allowed
});
writeLog(`[${new Date().toISOString()}] ADMIN /api/admin/clients/${clientId}/food-plan POST HIT by admin_id: ${adminUserId}`);
writeLog('Received food plan data from admin:', JSON.stringify(req.body, null, 2));
if (!hourly_plan || typeof hourly_plan !== 'object') {
return res.status(400).json({ error: 'Hourly plan data is missing or invalid.' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
writeLog(`Admin food plan save for client ${clientId}: Transaction started.`);
// Find the latest consultation for the client using the new user_id
const [consultations] = await executeSql(connection,
`SELECT client_consultation_id FROM client_consultations WHERE user_id = ? AND is_latest = TRUE`,
[clientId]
);
if (consultations.length === 0) {
throw new Error('No active consultation found for this client to save the food plan against.');
}
const consultationId = consultations[0].client_consultation_id;
// Delete any existing food plan for this specific consultation to avoid duplicates.
await executeSql(connection,
`DELETE FROM client_food_plans WHERE client_consultation_id = ?`,
[consultationId]
);
// Step 1: Insert the new food plan into client_food_plans
const [planResult] = await executeSql(connection, `
INSERT INTO client_food_plans (client_consultation_id, additional_personal_recommendations, created_by_admin_id)
VALUES (?, ?, ?)`,
[consultationId, sanitizedRecommendations || null, adminUserId]
);
const planId = planResult.insertId;
writeLog(`Admin food plan save: Inserted into client_food_plans, planId: ${planId}`);
// Step 2: Prepare and insert hourly details
const hourlyDetailsToInsert = [];
for (const timeSlot in hourly_plan) {
if (hourly_plan.hasOwnProperty(timeSlot)) {
const slotData = hourly_plan[timeSlot];
if (slotData.present_intake || slotData.proposed_structure || slotData.additional_points) {
hourlyDetailsToInsert.push([
planId, timeSlot, slotData.present_intake || null,
slotData.proposed_structure || null, slotData.additional_points || null
]);
}
}
}
if (hourlyDetailsToInsert.length > 0) {
await connection.query(
'INSERT INTO client_food_plan_hourly_details (plan_id, time_slot, present_intake, proposed_structure, additional_points) VALUES ?',
[hourlyDetailsToInsert]
);
writeLog(`Admin food plan save: Inserted ${hourlyDetailsToInsert.length} hourly details for planId: ${planId}`);
}
await connection.commit();
writeLog(`Admin food plan save for client ${clientId}: Transaction committed successfully.`);
res.json({ message: 'Client food plan updated successfully by admin.', planId: planId });
} catch (error) {
if (connection) await connection.rollback();
writeLog(`Error saving food plan for client ${clientId} by admin (ROLLBACK EXECUTED):`, error);
res.status(500).json({ error: 'Failed to save client food plan.' });
} finally {
if (connection) connection.release();
}
});
// Admin: Get latest medical history for a specific client
router.get('/api/admin/clients/:clientId/medical-history/latest', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
writeLog(`[${new Date().toISOString()}] ADMIN /api/admin/clients/${clientId}/medical-history/latest HIT`);
try {
const [latestHistoryMeta] = await executeSql(db, `
SELECT cmh.history_id, cmh.family_medical_history, cc.is_finalized, cc.is_food_plan_complete, cmh.created_at, cmh.updated_at
FROM client_medical_history cmh
JOIN client_consultations cc ON cmh.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ? AND cc.is_latest = 1
ORDER BY cmh.updated_at DESC
LIMIT 1`,
[clientId]
);
if (latestHistoryMeta.length === 0) {
return res.json({ message: 'No medical history found for this client.' });
}
const history = latestHistoryMeta[0];
const historyId = history.history_id;
const [medications] = await executeSql(db,
`SELECT medication_id, diagnosis, medicine_name, power, timing, since_when
FROM client_medications
WHERE history_id = ?`,
[historyId]
);
res.json({ ...history, medications: medications });
} catch (error) {
writeLog(`Error fetching latest medical history for client ${clientId} by admin:`, error);
res.status(500).json({ error: 'Failed to fetch latest medical history for client.' });
}
});
// Admin: Get personal details for a specific client
router.get('/api/admin/clients/:clientId/personal-details', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
writeLog(`[${new Date().toISOString()}] ADMIN /api/admin/clients/${clientId}/personal-details HIT`);
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.mobile_number, u.email,
u.address_1, u.address_2, u.address_3, u.city, u.pincode,
u.reference_source,
u.assigned_executive_id as enrolled_by_executive_id,
u.assigned_nutritionist_id as nutritionist_id,
u.created_at as registration_date,
u.is_email_verified,
u.is_active as is_account_active,
cc.height_cms, cc.weight_kg, cc.age_years, cc.gender, cc.marital_status,
cc.shift_duty, cc.joint_family, cc.is_vegetarian, cc.is_vegan, cc.is_jain,
cc.has_lactose_intolerance, cc.date_of_payment,
cc.health_issues, cc.food_liking, cc.food_disliking,
cc.job_description, cc.job_timings, cc.sedentary_status, cc.travelling_frequency
FROM users_v2 u
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
WHERE u.user_id = ?`,
[clientId]
);
if (clients.length === 0) {
return res.status(404).json({ error: 'Client not found' });
}
res.json(clients[0]);
} catch (error) {
writeLog(`Error fetching personal details for client ${clientId} by admin:`, error);
res.status(500).json({ error: 'Failed to fetch client personal details.' });
}
});
// Admin: Get latest blood test results for a specific client
router.get('/api/admin/clients/:clientId/blood-tests/latest', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
writeLog(`[${new Date().toISOString()}] ADMIN /api/admin/clients/${clientId}/blood-tests/latest HIT`);
try {
// 1. Get the latest report_id for the client
const [latestReportMeta] = await executeSql(db, `
SELECT cbtr.report_id, cbtr.report_date, cbtr.created_at
FROM client_blood_test_reports cbtr
JOIN client_consultations cc ON cbtr.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ? AND cc.is_latest = 1
ORDER BY cbtr.created_at DESC
LIMIT 1`,
[clientId]
);
if (latestReportMeta.length === 0) {
return res.json({ message: 'No blood test reports found for this client.' });
}
const report = latestReportMeta[0];
const reportId = report.report_id;
// 2. Get all results for that report_id
const [results] = await executeSql(db,
`SELECT test_code, value
FROM client_blood_test_results
WHERE report_id = ?`,
[reportId]
);
res.json({ ...report, results: results });
} catch (error) {
writeLog(`Error fetching latest blood tests for client ${clientId} by admin:`, error);
res.status(500).json({ error: 'Failed to fetch latest blood tests for client.' });
}
});
// Admin: Unfinalize/Re-open client's latest medical history
router.patch('/api/admin/clients/:clientId/medical-history/unfinalize', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const adminUserId = req.user.userId; // Admin performing the action
writeLog(`[${new Date().toISOString()}] ADMIN ${adminUserId} /api/admin/clients/${clientId}/medical-history/unfinalize HIT`);
const connection = await db.getConnection();
try {
await connection.beginTransaction();
const [submissionResult] = await executeSql(connection, `
UPDATE client_consultations SET is_finalized = FALSE WHERE user_id = ? AND is_latest = TRUE`,
[clientId]
);
writeLog('Final Submission set to FALSE for client ', clientId);
await connection.commit();
res.json({ message: `Client ${clientId}'s forms (Personal Details, Blood Tests, Food Plan, Medical History) have been re-opened for edits.` });
} catch (error) {
if (connection) await connection.rollback();
writeLog(`Error unfinalizing medical history for client ${clientId} by admin ${adminUserId}:`, error);
res.status(500).json({ error: 'Failed to re-open medical history.' });
} finally {
if (connection) connection.release();
}
});
router.patch('/api/admin/clients/:clientId/food-plan/complete', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const adminUserId = req.user.userId; // Admin performing the action
writeLog(`[${new Date().toISOString()}] ADMIN ${adminUserId} /api/admin/clients/${clientId}/food-plan/complete HIT`);
const connection = await db.getConnection();
try {
await connection.beginTransaction();
await executeSql(connection, `
UPDATE client_consultations SET is_food_plan_complete = TRUE WHERE user_id = ? AND is_latest = TRUE`,
[clientId]
);
writeLog('Food plan marked as complete for client ', clientId);
const [users] = await executeSql(db, `SELECT email, first_name FROM users_v2 WHERE user_id = ?`,
[clientId]);
if (users.length > 0) {
const client = users[0];
const mailOptions = {
from: `"Consultation Service" <${EMAIL_USER}>`,
to: client.email,
subject: 'Your Food Plan is Ready!',
html: `
Dear ${client.first_name},
Great news! Your personalized food plan has been prepared and is now available for you to view.
You can log in to your account to access it.
If you have any questions, please feel free to contact us.
Sincerely,
The Consultation Service Team
`,
};
if (EMAIL_USER !== '') {
try {
await transporter.sendMail(mailOptions);
writeLog(`Sent food plan completion email to ${client.email}`);
} catch (emailError) {
writeLog('Error sending food plan completion email:', emailError);
// Don't fail the whole request, but log the error
}
} else {
writeLog(`Could not send food plan completion email to ${client.email} due to missing email setup.`);
}
}
await connection.commit();
res.json({ message: `Client ${clientId}'s food plan has been marked as complete.` });
} catch (error) {
if (connection) await connection.rollback();
writeLog(`Error completing food plan for client ${clientId} by admin ${adminUserId}:`, error);
res.status(500).json({ error: 'Failed to complete food plan.' });
} finally {
if (connection) connection.release();
}
});
// Get statistics counts
router.get('/api/admin/stats/counts', verifyAdminToken, async (req, res) => {
try {
const [results] = await executeSql(db, `
SELECT
(SELECT COUNT(DISTINCT u.user_id) FROM users_v2 u JOIN user_roles ur ON u.user_id = ur.user_id JOIN roles r ON ur.role_id = r.role_id WHERE r.role_name = 'nutritionist' AND u.is_active = TRUE) as nutritionist_count,
(SELECT COUNT(DISTINCT u.user_id) FROM users_v2 u JOIN user_roles ur ON u.user_id = ur.user_id JOIN roles r ON ur.role_id = r.role_id WHERE r.role_name = 'executive' AND u.is_active = TRUE) as executive_count,
(SELECT COUNT(DISTINCT u.user_id) FROM users_v2 u JOIN user_roles ur ON u.user_id = ur.user_id JOIN roles r ON ur.role_id = r.role_id WHERE r.role_name = 'client' AND u.is_active = TRUE) as client_count,
(SELECT COUNT(DISTINCT u.user_id) FROM users_v2 u JOIN user_roles ur ON u.user_id = ur.user_id JOIN roles r ON ur.role_id = r.role_id WHERE r.role_name = 'client' AND u.is_active = FALSE) as pending_activation_count,
(SELECT COUNT(*) FROM client_consultations WHERE is_latest = 1 AND is_finalized = 1) as final_history_submitted_count,
(SELECT COUNT(DISTINCT cfp.client_consultation_id) FROM client_food_plans cfp JOIN client_consultations cc ON cfp.client_consultation_id = cc.client_consultation_id WHERE cc.is_latest = 1) as food_plan_completed_count,
(SELECT COUNT(*) FROM client_consultations WHERE is_latest = 1 AND is_food_plan_complete = 1) as food_plan_sent_count;
`);
const counts = results[0];
res.json(counts);
} catch (error) {
writeLog('Error fetching statistics counts:', error);
res.status(500).json({ error: 'Failed to fetch statistics' });
}
});
// Admin: Get current admin's details for welcome message
router.get('/api/admin/me', verifyAdminToken, async (req, res) => {
const adminId = req.user.userId;
try {
const [admins] = await executeSql(db, `
SELECT first_name, last_name FROM users_v2 WHERE user_id = ?
`, [adminId]);
if (admins.length === 0) {
return res.status(404).json({ error: 'Admin user not found in database.' });
}
res.json(admins[0]);
} catch (error) {
writeLog(`Error fetching details for admin ${adminId}:`, error);
res.status(500).json({ error: 'Failed to fetch admin details.' });
}
});
// Nutritionist: Get current nutritionist's details for welcome message
router.get('/api/nutritionist/me', verifyNutritionistToken, async (req, res) => {
const nutritionistId = req.user.userId;
try {
const [nutritionists] = await executeSql(db, `
SELECT user_id, first_name, last_name FROM users_v2 WHERE user_id = ?
`, [nutritionistId]);
if (nutritionists.length === 0) {
return res.status(404).json({ error: 'Nutritionist user not found in database.' });
}
res.json(nutritionists[0]);
} catch (error) {
writeLog(`Error fetching details for nutritionist ${nutritionistId}:`, error);
res.status(500).json({ error: 'Failed to fetch nutritionist details.' });
}
});
router.get('/api/nutritionist/stats/counts', verifyNutritionistToken, async (req, res) => {
const nutritionistId = req.user.userId;
try {
const [results] = await executeSql(db, `
SELECT
(SELECT COUNT(user_id) FROM users_v2 WHERE assigned_nutritionist_id = ? AND is_active = TRUE) as total_assigned_clients_count,
(SELECT COUNT(cc.client_consultation_id) FROM client_consultations cc JOIN users_v2 u ON cc.user_id = u.user_id WHERE u.assigned_nutritionist_id = ? AND cc.is_latest = 1 AND cc.is_finalized = 1) as final_history_submitted_count,
(SELECT COUNT(DISTINCT cc.client_consultation_id) FROM client_food_plans cfp JOIN client_consultations cc ON cfp.client_consultation_id = cc.client_consultation_id JOIN users_v2 u ON cc.user_id = u.user_id WHERE u.assigned_nutritionist_id = ? AND cc.is_latest = 1) as food_plan_suggested_count,
(SELECT COUNT(cc.client_consultation_id) FROM client_consultations cc JOIN users_v2 u ON cc.user_id = u.user_id WHERE u.assigned_nutritionist_id = ? AND cc.is_latest = 1 AND cc.is_food_plan_complete = 1) as food_plan_sent_count;
`, [nutritionistId, nutritionistId, nutritionistId, nutritionistId]);
const counts = results[0];
res.json(counts);
} catch (error) {
writeLog(`Error fetching statistics for nutritionist ${nutritionistId}:`, error);
res.status(500).json({ error: 'Failed to fetch nutritionist statistics' });
}
});
// Nutritionist: Get all assigned clients
router.get('/api/nutritionist/my-clients', verifyNutritionistToken, async (req, res) => {
const nutritionistId = req.user.userId;
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number,
u.created_at as registration_date,
cc.is_finalized,
(SELECT COUNT(*) > 0 FROM client_food_plans cfp WHERE cfp.client_consultation_id = cc.client_consultation_id) as has_food_plan_suggested,
cc.is_food_plan_complete
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id AND ur.role_id = (SELECT role_id FROM roles WHERE role_name = 'client')
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
WHERE u.assigned_nutritionist_id = ?
ORDER BY u.user_id DESC
`, [nutritionistId]);
res.json(clients);
} catch (error) {
writeLog(`Error fetching clients for nutritionist ${nutritionistId}:`, error);
res.status(500).json({ error: 'Failed to fetch assigned clients' });
}
});
// Nutritionist: Search assigned clients
router.get('/api/nutritionist/my-clients/search', verifyNutritionistToken, async (req, res) => {
const nutritionistId = req.user.userId;
try {
let baseQuery = `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number,
u.created_at as registration_date,
cc.is_finalized,
(SELECT COUNT(*) > 0 FROM client_food_plans cfp WHERE cfp.client_consultation_id = cc.client_consultation_id) as has_food_plan_suggested,
cc.is_food_plan_complete
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id AND ur.role_id = (SELECT role_id FROM roles WHERE role_name = 'client')
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
`;
const conditions = ["u.assigned_nutritionist_id = ?"];
const params = [nutritionistId];
if (req.query.client_id) { conditions.push("u.user_id = ?"); params.push(req.query.client_id); }
if (req.query.first_name) { conditions.push("u.first_name LIKE ?"); params.push(`%${req.query.first_name}%`); }
if (req.query.last_name) { conditions.push("u.last_name LIKE ?"); params.push(`%${req.query.last_name}%`); }
if (req.query.email) { conditions.push("u.email LIKE ?"); params.push(`%${req.query.email}%`); }
baseQuery += " WHERE " + conditions.join(" AND ");
baseQuery += " ORDER BY u.created_at DESC";
const [clients] = await executeSql(db, baseQuery, params);
res.json(clients);
} catch (error) {
writeLog(`Error searching clients for nutritionist ${nutritionistId}:`, error);
res.status(500).json({ error: 'Failed to search assigned clients' });
}
});
router.get('/api/executive/stats/counts', verifyExecutiveToken, async (req, res) => {
const executiveId = req.user.userId;
try {
const [results] = await executeSql(db, `
SELECT
(SELECT COUNT(user_id) FROM users_v2 WHERE assigned_executive_id = ? AND is_active = TRUE) as total_enrolled_clients_count,
(SELECT COUNT(user_id) FROM users_v2 WHERE assigned_executive_id = ? AND is_active = FALSE) as clients_pending_activation_count,
(SELECT COUNT(cc.client_consultation_id) FROM client_consultations cc JOIN users_v2 u ON cc.user_id = u.user_id WHERE u.assigned_executive_id = ? AND cc.is_latest = 1 AND cc.is_finalized = 1) as final_history_submitted_count,
(SELECT COUNT(DISTINCT cc.client_consultation_id) FROM client_food_plans cfp JOIN client_consultations cc ON cfp.client_consultation_id = cc.client_consultation_id JOIN users_v2 u ON cc.user_id = u.user_id WHERE u.assigned_executive_id = ? AND cc.is_latest = 1) as food_plan_suggested_count,
(SELECT COUNT(cc.client_consultation_id) FROM client_consultations cc JOIN users_v2 u ON cc.user_id = u.user_id WHERE u.assigned_executive_id = ? AND cc.is_latest = 1 AND cc.is_food_plan_complete = 1) as food_plan_sent_count;
`, [executiveId, executiveId, executiveId, executiveId, executiveId]);
const counts = results[0];
res.json(counts);
} catch (error) {
writeLog(`Error fetching statistics for executive ${executiveId}:`, error);
res.status(500).json({ error: 'Failed to fetch executive statistics' });
}
});
// Executive: Get all enrolled clients
router.get('/api/executive/my-clients', verifyExecutiveToken, async (req, res) => {
const executiveId = req.user.userId;
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number,
u.created_at as registration_date,
u.is_active as is_account_active,
cc.is_finalized,
(SELECT COUNT(*) > 0 FROM client_food_plans cfp WHERE cfp.client_consultation_id = cc.client_consultation_id) as has_food_plan_suggested,
cc.is_food_plan_complete
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id AND ur.role_id = (SELECT role_id FROM roles WHERE role_name = 'client')
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
WHERE u.assigned_executive_id = ?
ORDER BY u.user_id DESC
`, [executiveId]);
res.json(clients);
} catch (error) {
writeLog(`Error fetching clients for executive ${executiveId}:`, error);
res.status(500).json({ error: 'Failed to fetch enrolled clients' });
}
});
// Executive: Search enrolled clients
router.get('/api/executive/my-clients/search', verifyExecutiveToken, async (req, res) => {
const executiveId = req.user.userId;
try {
let baseQuery = `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number,
u.created_at as registration_date,
u.is_active as is_account_active,
cc.is_finalized,
(SELECT COUNT(*) > 0 FROM client_food_plans cfp WHERE cfp.client_consultation_id = cc.client_consultation_id) as has_food_plan_suggested,
cc.is_food_plan_complete
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id AND ur.role_id = (SELECT role_id FROM roles WHERE role_name = 'client')
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
`;
const conditions = ["u.assigned_executive_id = ?"];
const params = [executiveId];
if (req.query.client_id) { conditions.push("u.user_id = ?"); params.push(req.query.client_id); }
if (req.query.first_name) { conditions.push("u.first_name LIKE ?"); params.push(`%${req.query.first_name}%`); }
if (req.query.last_name) { conditions.push("u.last_name LIKE ?"); params.push(`%${req.query.last_name}%`); }
if (req.query.email) { conditions.push("u.email LIKE ?"); params.push(`%${req.query.email}%`); }
baseQuery += " WHERE " + conditions.join(" AND ");
baseQuery += " ORDER BY u.created_at DESC";
const [clients] = await executeSql(db, baseQuery, params);
res.json(clients);
} catch (error) {
writeLog(`Error searching clients for executive ${executiveId}:`, error);
res.status(500).json({ error: 'Failed to search enrolled clients' });
}
});
// Executive: Get current executive's details for welcome message
router.get('/api/executive/me', verifyExecutiveToken, async (req, res) => {
const executiveId = req.user.userId;
try {
const [executives] = await executeSql(db, `
SELECT first_name, last_name FROM users_v2 WHERE user_id = ?
`, [executiveId]);
if (executives.length === 0) {
return res.status(404).json({ error: 'Executive user not found in database.' });
}
res.json(executives[0]);
} catch (error) {
writeLog(`Error fetching details for executive ${executiveId}:`, error);
res.status(500).json({ error: 'Failed to fetch executive details.' });
}
});
// Admin: Delete Client (Permanent hard delete of user and all associated data)
router.delete('/api/admin/clients/:clientId', verifyAdminToken, async (req, res) => {
const { clientId } = req.params;
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// 1. Get all consultation IDs for the client
const [consultations] = await executeSql(connection,
'SELECT client_consultation_id FROM client_consultations WHERE user_id = ?',
[clientId]
);
const consultationIds = consultations.map(c => c.client_consultation_id);
if (consultationIds.length > 0) {
// 2. Get all food plan IDs for these consultations
const [foodPlans] = await executeSql(connection,
`SELECT plan_id FROM client_food_plans WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
const foodPlanIds = foodPlans.map(fp => fp.plan_id);
// 3. Delete hourly food plan details
if (foodPlanIds.length > 0) {
await executeSql(connection,
`DELETE FROM client_food_plan_hourly_details WHERE plan_id IN (${foodPlanIds.map(() => '?').join(',')})`,
foodPlanIds
);
}
// 4. Delete client food plans
await executeSql(connection,
`DELETE FROM client_food_plans WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
// 5. Get all medical history IDs for these consultations
const [medicalHistories] = await executeSql(connection,
`SELECT history_id FROM client_medical_history WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
const historyIds = medicalHistories.map(mh => mh.history_id);
// 5.1 Get all blood test report IDs for these consultations
const [bloodReports] = await executeSql(connection,
`SELECT report_id FROM client_blood_test_reports WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
const reportIds = bloodReports.map(br => br.report_id);
// 5.2 Delete blood test results
if (reportIds.length > 0) {
await executeSql(connection,
`DELETE FROM client_blood_test_results WHERE report_id IN (${reportIds.map(() => '?').join(',')})`,
reportIds
);
}
// 6. Delete client medications
if (historyIds.length > 0) {
await executeSql(connection,
`DELETE FROM client_medications WHERE history_id IN (${historyIds.map(() => '?').join(',')})`,
historyIds
);
}
// 7. Delete client medical history
await executeSql(connection,
`DELETE FROM client_medical_history WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
// 7.1 Delete client blood test reports
await executeSql(connection,
`DELETE FROM client_blood_test_reports WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
}
// 8. Delete client consultations
await executeSql(connection, 'DELETE FROM client_consultations WHERE user_id = ?', [clientId]);
// 9. Delete user roles
await executeSql(connection, 'DELETE FROM user_roles WHERE user_id = ?', [clientId]);
// 10. Hard delete user from users_v2 table (Right to Erasure)
await executeSql(connection, 'DELETE FROM users_v2 WHERE user_id = ?', [clientId]);
await connection.commit();
res.json({ message: 'Client account and all associated data permanently deleted successfully.' });
} catch (error) {
if (connection) await connection.rollback();
writeLog(`Error deleting client account (Admin initiated) for client ID ${clientId}:`, error);
res.status(500).json({ error: 'Failed to delete client account.' });
} finally {
if (connection) connection.release();
}
});
// Backup Database Route
router.get('/api/admin/backup-db', verifyAdminToken, (req, res) => {
if (req.user.email !== 'madhavjoshi02@gmail.com') {
return res.status(403).json({ error: 'Forbidden: You do not have permission to download database backups.' });
}
const now = new Date();
const day = String(now.getDate()).padStart(2, '0');
const month = String(now.getMonth() + 1).padStart(2, '0');
const year = now.getFullYear();
const filename = `consultation_backup_${day}${month}${year}.sql`;
const mysqldumpPath = path.join(process.env.MYSQL_BIN_PATH || '/usr/bin', 'mysqldump');
const dumpCommand = `"${mysqldumpPath}" --plugin-dir="${process.env.MYSQL_PLUGIN_PATH}" --default-auth=mysql_native_password -h ${process.env.DB_HOST} -u ${process.env.DB_USER} -p${process.env.DB_PASSWORD} ${process.env.DB_DATABASE}`;
exec(dumpCommand, (error, stdout, stderr) => {
if (error) {
writeLog('Database backup error:', error);
return res.status(500).json({ error: 'Error creating database backup.' });
}
res.setHeader('Content-Type', 'application/sql');
res.setHeader('Content-Disposition', `attachment; filename=${filename}`);
res.send(stdout);
});
});
}
--- authRoutes.mjs ---
import bcrypt from 'bcrypt';
import jwt from 'jsonwebtoken';
import crypto from 'crypto';
import { executeSql } from './common/database.mjs';
import { writeLog, checkPasswordPolicy } from './common/utils.mjs';
export function setupAuthRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl }) {
// Unified Login
router.post('/api/login', async (req, res) => {
const { email, password } = req.body;
if (!email || !password) {
return res.status(400).json({ error: 'Email and password are required' });
}
try {
const [users] = await executeSql(db,
`SELECT u.user_id, u.email, u.password_hash, u.is_active, u.is_email_verified, GROUP_CONCAT(r.role_name) as roles
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
WHERE u.email = ? AND u.deleted = 0
GROUP BY u.user_id`,
[email]
);
if (users.length === 0) {
return res.status(401).json({ error: 'Invalid credentials' });
}
const user = users[0];
const isPasswordValid = await bcrypt.compare(password, user.password_hash);
if (!isPasswordValid) {
return res.status(401).json({ error: 'Invalid credentials' });
}
const userRoles = user.roles ? user.roles.split(',') : [];
// Role-specific checks
if (userRoles.includes('client')) {
if (!user.is_email_verified) {
return res.status(403).json({ error: 'Email not verified. Please verify your email first.' });
}
if (!user.is_active) {
return res.status(403).json({ error: 'Your account has not been activated by the admin yet. Please wait for the activation email.' });
}
} else if (!userRoles.some(role => ['admin', 'nutritionist', 'executive'].includes(role))) {
// If user is not a client and not a staff/admin, they can't log in.
return res.status(403).json({ error: 'You do not have a role that can log in.' });
}
// Update last_login for data retention policy tracking
await executeSql(db, 'UPDATE users_v2 SET last_login = CURRENT_TIMESTAMP WHERE user_id = ?', [user.user_id]);
const token = jwt.sign(
{ userId: user.user_id, email: user.email, roles: userRoles },
JWT_SECRET,
{ expiresIn: '1h' }
);
res.cookie('token', token, {
httpOnly: true,
secure: process.env.NODE_ENV === 'production',
sameSite: 'Strict',
maxAge: 3600000, // 1 hour
path: '/'
});
res.json({ message: 'Login successful', roles: userRoles });
} catch (error) {
writeLog('Login error:', error);
res.status(500).json({ error: 'Login failed' });
}
});
// Unified Forgot Password
router.post('/api/forgot-password', async (req, res) => {
const { email } = req.body;
writeLog(`[Unified Forgot Pwd] Request for email: ${email}`);
if (email) {
try {
const [users] = await executeSql(db,
`SELECT u.user_id, u.first_name, GROUP_CONCAT(r.role_name) as roles
FROM users_v2 u
LEFT JOIN user_roles ur ON u.user_id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.role_id
WHERE u.email = ? AND u.deleted = 0
GROUP BY u.user_id`,
[email]
);
if (users.length > 0) {
const user = users[0];
const userRoles = user.roles ? user.roles.split(',') : [];
// Check if user has any valid role
if (userRoles.some(role => ['client', 'admin', 'nutritionist', 'executive'].includes(role))) {
const resetToken = crypto.randomBytes(32).toString('hex');
const resetTokenExpiry = new Date(Date.now() + 3600000); // Token expires in 1 hour
await executeSql(db,
'UPDATE users_v2 SET password_reset_token = ?, password_reset_expires_at = ? WHERE user_id = ?',
[resetToken, resetTokenExpiry, user.user_id]
);
const baseUrl = appUrl.endsWith('/') ? appUrl : `${appUrl}/`;
const resetLink = `${baseUrl}reset-password.html?token=${resetToken}`;
const mailOptions = {
from: `"Consultation Service" <${EMAIL_USER}>`,
to: email,
subject: 'Password Reset Request',
html: `Dear ${user.first_name},
You requested a password reset. Click here to reset your password. This link will expire in 1 hour.
If you did not request this, please ignore this email.
`
};
if (EMAIL_USER && transporter) {
await transporter.sendMail(mailOptions);
writeLog(`[Unified Forgot Pwd] Password reset email sent to ${email}`);
} else {
writeLog(`[Unified Forgot Pwd] Email not sent for password reset to ${email}. Token for testing: ${resetToken}`);
}
return res.json({ message: 'Password reset link has been sent to your email.' });
} else {
writeLog(`[Unified Forgot Pwd] No valid role found for user: ${email}`);
return res.status(403).json({ error: 'This account is not authorized for password reset.' });
}
} else {
writeLog(`[Unified Forgot Pwd] No active user found with email: ${email}`);
return res.status(404).json({ error: 'No active account found with this email address.' });
}
} catch (error) {
writeLog('[Unified Forgot Pwd] Error during DB/token operations:', error);
return res.status(500).json({ error: 'An error occurred while processing your request.' });
}
}
return res.status(400).json({ error: 'Email address is required.' });
});
// Unified Reset Password
router.post('/api/reset-password', async (req, res) => {
const { token, newPassword } = req.body;
writeLog(`[Unified Reset Pwd] Attempt with token: ${token ? token.substring(0, 10) + '...' : 'No Token'}`);
if (!token || !newPassword) {
return res.status(400).json({ error: 'Token and new password are required.' });
}
const passwordPolicyResult = checkPasswordPolicy(newPassword);
if (!passwordPolicyResult.isValid) {
return res.status(400).json({ error: passwordPolicyResult.message });
}
try {
const [users] = await executeSql(db,
"SELECT user_id, password_reset_expires_at FROM users_v2 WHERE password_reset_token = ? AND deleted = 0",
[token]
);
if (users.length === 0) {
return res.status(400).json({ error: 'Invalid or expired password reset token.' });
}
const user = users[0];
if (new Date() > new Date(user.password_reset_expires_at)) {
await executeSql(db, 'UPDATE users_v2 SET password_reset_token = NULL, password_reset_expires_at = NULL WHERE user_id = ?', [user.user_id]);
return res.status(400).json({ error: 'Password reset token has expired.' });
}
const newPasswordHash = await bcrypt.hash(newPassword, 10);
await executeSql(db,
'UPDATE users_v2 SET password_hash = ?, password_reset_token = NULL, password_reset_expires_at = NULL WHERE user_id = ?',
[newPasswordHash, user.user_id]
);
writeLog(`Password reset successfully for user ID: ${user.user_id}`);
res.json({ message: 'Password has been reset successfully. You can now login with your new password.' });
} catch (error) {
writeLog('Error resetting password:', error);
res.status(500).json({ error: 'Failed to reset password. The link may be invalid or expired. Please try again.' });
}
});
// API endpoint to send a login OTP to a client's email
router.post('/api/client/login-otp/send', async (req, res) => {
const { email } = req.body;
writeLog(`[${new Date().toISOString()}] /api/client/login-otp/send request for email: ${email}`);
if (email) {
try {
const [users] = await executeSql(db,
`SELECT u.user_id, u.first_name, u.is_email_verified, u.is_active, GROUP_CONCAT(r.role_name) as roles
FROM users_v2 u
LEFT JOIN user_roles ur ON u.user_id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.role_id
WHERE u.email = ? AND u.deleted = 0
GROUP BY u.user_id`,
[email]
);
if (users.length > 0) {
const user = users[0];
const userRoles = user.roles ? user.roles.split(',') : [];
// Only proceed if the user is a client, is verified, and is active.
if (userRoles.includes('client') && user.is_email_verified && user.is_active) {
const generateOTP = () => Math.floor(100000 + Math.random() * 900000).toString();
const otp = generateOTP();
const otpExpiry = new Date(Date.now() + 10 * 60 * 1000); // OTP expires in 10 minutes
await executeSql(db,
'UPDATE users_v2 SET email_otp = ?, email_otp_expires_at = ? WHERE user_id = ?',
[otp, otpExpiry, user.user_id]
);
const mailOptions = {
from: `"Consultation Service" <${EMAIL_USER}>`,
to: email,
subject: 'Your Login OTP for Consultation Service',
html: `Dear ${user.first_name},
Your One-Time Password (OTP) for login is: ${otp}
This OTP will expire in 10 minutes.
If you did not request this, please ignore this email.
`
};
if (EMAIL_USER && transporter) {
await transporter.sendMail(mailOptions);
writeLog(`[OTP Login] Login OTP sent to ${email}`);
return res.json({ message: 'Login OTP sent successfully.' });
} else {
writeLog(`[OTP Login] Email not sent to ${email}. EMAIL_USER not configured. OTP for testing: ${otp}`);
return res.json({ message: 'Login OTP generated (Check Server Logs).' });
}
} else {
writeLog(`[OTP Login] No OTP sent. User ${email} is either not a client, not verified, or not active. Roles: ${userRoles}, is_email_verified: ${user.is_email_verified}, is_active: ${user.is_active}`);
return res.status(403).json({ error: 'User is either not a client, not verified, or not active.' });
}
} else {
writeLog(`[OTP Login] No user found with email: ${email}`);
return res.status(404).json({ error: 'No active user found with this email.' });
}
} catch (error) {
writeLog('Error sending login OTP:', error);
return res.status(500).json({ error: 'An error occurred while processing your request.' });
}
}
return res.status(400).json({ error: 'Email address is required.' });
});
// Client Verify Login OTP
router.post('/api/client/login-otp/verify', async (req, res) => {
const { email, otp } = req.body;
if (!email || !otp) {
return res.status(400).json({ error: 'Email and OTP are required.' });
}
try {
const [users] = await executeSql(db,
'SELECT user_id, email, is_active, is_email_verified FROM users_v2 WHERE email = ? AND email_otp = ? AND email_otp_expires_at > NOW() AND deleted = 0',
[email, otp]
);
if (users.length === 0) {
return res.status(400).json({ error: 'Invalid or expired OTP.' });
}
const user = users[0];
if (!user.is_email_verified) {
return res.status(403).json({ error: 'Your email address has not been verified.' });
}
console.log(`LOGIN CHECK: User ${user.user_id} has is_active status of:`, user.is_active);
if (!user.is_active) {
return res.status(403).json({ error: 'Your account has not been activated by the admin yet. Please wait for the activation email.' });
}
// Update last_login for data retention policy tracking
await executeSql(db, 'UPDATE users_v2 SET last_login = CURRENT_TIMESTAMP WHERE user_id = ?', [user.user_id]);
// Clear the OTP from the table
await executeSql(db, 'UPDATE users_v2 SET email_otp = NULL, email_otp_expires_at = NULL WHERE user_id = ?', [user.user_id]);
const [roleRows] = await executeSql(db, 'SELECT r.role_name FROM user_roles ur JOIN roles r ON ur.role_id = r.role_id WHERE ur.user_id = ?', [user.user_id]);
const roles = roleRows.map(r => r.role_name);
const token = jwt.sign({ userId: user.user_id, email: user.email, roles: roles }, JWT_SECRET, { expiresIn: '1h' });
res.cookie('token', token, {
httpOnly: true,
secure: process.env.NODE_ENV === 'production',
sameSite: 'Strict',
maxAge: 3600000, // 1 hour
path: '/'
});
res.json({ message: 'Login successful' });
} catch (error) {
writeLog('Error verifying login OTP:', error);
res.status(500).json({ error: 'Login failed. Please try again.' });
}
});
// Logout route to clear the cookie
router.all('/api/logout', (req, res) => {
const options = {
httpOnly: true,
secure: process.env.NODE_ENV === 'production',
sameSite: 'Strict'
};
res.clearCookie('token', { ...options, path: '/' });
writeLog(`[Logout] Cookie cleared for user. Path: ${req.path}, Method: ${req.method}`);
res.json({ message: 'Logout successful' });
});
}
--- clientRoutes.mjs ---
import bcrypt from 'bcrypt';
import crypto from 'crypto';
import jwt from 'jsonwebtoken';
import { writeLog } from './common/utils.mjs';
import sanitizeHtml from 'sanitize-html';
import { checkPasswordPolicy } from './common/utils.mjs';
import { executeSql } from './common/database.mjs';
import { isStaffAuthorizedForClient } from './staffRoutes.mjs';
export function setupClientRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl }) {
router.post('/api/client/update-staff-preference', async (req, res) => {
// IMPORTANT: In a real app, get client_id from a verified client JWT
const { client_id, nutritionist_id, executive_id } = req.body;
if (!client_id) {
return res.status(400).json({ error: 'Client ID is required.' });
}
// Use null as default if no selection is made or if '0' or empty string is passed
const finalNutritionistId = (nutritionist_id && nutritionist_id !== "0" && nutritionist_id !== "") ? nutritionist_id : null;
const finalExecutiveId = (executive_id && executive_id !== "0" && executive_id !== "") ? executive_id : null;
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Update new table
const [result] = await executeSql(connection,
`UPDATE users_v2 SET assigned_nutritionist_id = ?, assigned_executive_id = ?
WHERE user_id = (SELECT new_user_id FROM client_id_to_user_id_mapping WHERE old_client_id = ?)`,
[finalNutritionistId, finalExecutiveId, client_id]
);
if (result.affectedRows === 0) {
await connection.rollback();
return res.status(404).json({ error: 'Client not found or no update made.' });
}
await connection.commit();
res.json({ message: 'Staff preferences updated successfully.' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error updating client staff preferences:', error);
res.status(500).json({ error: 'Failed to update staff preferences.' });
} finally {
if (connection) connection.release();
}
});
// 1. Client Registration
router.post('/register', async (req, res) => {
const connection = await db.getConnection();
writeLog('Registration payload received:', JSON.stringify(req.body, null, 2));
const { first_name, last_name, mobile_number, email, password, consent_given, date_of_birth } = req.body;
// Input Validation
if (!first_name || !last_name || !mobile_number || !email || !password) {
return res.status(400).json({ error: 'All fields are required' });
}
// Basic email format validation
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
if (!emailRegex.test(email)) {
return res.status(400).json({ error: 'Invalid email format' });
}
if (password.length < 6) {
return res.status(400).json({ error: 'Password must be at least 6 characters long' });
}
const passwordPolicyResult = checkPasswordPolicy(password);
if (!passwordPolicyResult.isValid) {
return res.status(400).json({ error: passwordPolicyResult.message });
}
const generateOTP = () => {
return Math.floor(100000 + Math.random() * 900000).toString(); // 6-digit OTP
};
const otp = generateOTP();
const otpExpiry = new Date();
otpExpiry.setMinutes(otpExpiry.getMinutes() + 10); // OTP expires in 10 minutes
const consentTimestamp = consent_given ? new Date() : null;
try {
await connection.beginTransaction();
// Check if email or mobile number already exists
const [existingV2Users] = await executeSql(connection,
'SELECT user_id, is_email_verified FROM users_v2 WHERE email = ? OR mobile_number = ?',
[email, mobile_number]
);
let newUserId;
let isNewUser = false;
if (existingV2Users.length > 0) {
const existingUser = existingV2Users[0];
if (existingUser.is_email_verified) {
await connection.rollback();
return res.status(409).json({ error: 'Email or mobile number already registered.' });
}
// User exists but is unverified. Allow re-registration by updating details.
const hashedPassword = await bcrypt.hash(password, 10);
await executeSql(connection,
'UPDATE users_v2 SET first_name=?, last_name=?, password_hash=?, mobile_number=?, email_otp=?, email_otp_expires_at=?, consent_given=?, consent_timestamp=?, date_of_birth=? WHERE user_id=?',
[first_name, last_name, hashedPassword, mobile_number, otp, otpExpiry, consent_given ? 1 : 0, consentTimestamp, date_of_birth, existingUser.user_id]
);
newUserId = existingUser.user_id;
} else {
const hashedPassword = await bcrypt.hash(password, 10);
// Insert new user with OTP details
const [newUserResult] = await executeSql(connection,
'INSERT INTO users_v2 (first_name, last_name, email, password_hash, mobile_number, email_otp, email_otp_expires_at, is_active, consent_given, consent_timestamp, date_of_birth) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
[first_name, last_name, email, hashedPassword, mobile_number, otp, otpExpiry, 0, consent_given ? 1 : 0, consentTimestamp, date_of_birth]
);
newUserId = newUserResult.insertId;
// Link user to 'client' role
const [clientRole] = await executeSql(connection, "SELECT role_id FROM roles WHERE role_name = 'client'");
if (clientRole.length === 0) throw new Error("Critical: 'client' role not found in roles table.");
await executeSql(connection, 'INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)', [newUserId, clientRole[0].role_id]);
// Create a corresponding entry in the client_consultations table
await executeSql(connection, 'INSERT INTO client_consultations (user_id) VALUES (?)', [newUserId]);
isNewUser = true;
}
await connection.commit();
// Send Admin Notification Email only if it's a new registration
if (isNewUser) {
const adminMailOptions = {
from: `"Consultation Service" <${EMAIL_USER}>`,
to: EMAIL_USER,
subject: 'New Client Registration',
html: `
Dear Admin,
A new client has registered and is pending email verification:
- Client ID: ${newUserId}
- Email: ${email}
- Name: ${first_name} ${last_name}
- Mobile: ${mobile_number}
`,
};
try {
if (EMAIL_USER !== '' && transporter) {
await transporter.sendMail(adminMailOptions);
writeLog(`Sent admin notification email for new client ${email}`);
}
} catch (adminEmailError) {
writeLog('Error sending admin notification email:', adminEmailError);
}
}
// *** Send Client OTP Email ***
const mailOptions = {
from: `"Consultation Service" <${EMAIL_USER}>`,
to: email,
subject: 'Your Email Verification OTP',
html: `
Dear ${first_name},
Thank you for registering. Your One-Time Password (OTP) for email verification is: ${otp}
This OTP will expire in 10 minutes. Please enter this code on the verification page to continue.
Sincerely,
The Consultation Service Team
`,
};
try {
if (EMAIL_USER !== '' && transporter) {
await transporter.sendMail(mailOptions);
writeLog(`Sent verification email to ${email}`);
} else {
if (EMAIL_USER === '') {
writeLog(`
-----> Generated OTP: ${otp}
`);
} else {
throw 'Email Transporter is not Configured';
}
}
} catch (emailError) {
writeLog('Error sending email:', emailError);
// Handle email sending failure gracefully. You might want to log this or retry.
// For now, we'll just proceed with registration but inform the client.
return res.status(500).json({
message: 'Client registered, but email sending failed. Please try again later.',
client_id: newUserId,
});
}
// If the email sends successfully, proceed with the registration success message:
res.status(201).json({ message: 'Client registered successfully', client_id: newUserId });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Registration error:', error);
res.status(500).json({ error: 'Registration failed' }); // Improved error message
} finally {
if (connection) connection.release();
}
});
// 2. Email OTP Verification
router.post('/verify-email', async (req, res) => {
const connection = await db.getConnection();
const { email, otp } = req.body;
// Input Validation for verify-email
if (!email || !otp) {
return res.status(400).json({ error: 'Email and OTP are required' });
}
try {
await connection.beginTransaction();
// Find the user in the new users_v2 table
const [users] = await executeSql(connection,
'SELECT user_id, first_name, last_name FROM users_v2 WHERE email = ? AND email_otp = ? AND email_otp_expires_at > NOW()',
[email, otp]
);
if (users.length === 0) {
await connection.rollback();
return res.status(400).json({ error: 'Invalid or expired OTP' });
}
const user = users[0];
// Update is_email_verified and clear OTP fields in the new table
await executeSql(connection,
'UPDATE users_v2 SET is_email_verified = TRUE, email_otp = NULL, email_otp_expires_at = NULL WHERE user_id = ?',
[user.user_id]
);
await connection.commit();
const welcomeMailOptions = {
from: `"Consultation Service" <${EMAIL_USER}>`,
to: email,
subject: 'Welcome - Next Steps & Payment Information',
html: `
Dear ${user.first_name},
Your email has been successfully verified! Welcome to our Consultation Service.
Next Steps: Payment of Fees
To activate your account, a one-time fee is required:
- Fee Amount: [Specify Fee Amount]
- Bank Details:
- Bank Name: [Bank Name]
- Account Name: [Account Name]
- Account Number: [Account Number]
- SWIFT/BIC Code: [SWIFT Code]
- Reference: ${email}
Once payment is made, please allow 24-48 hours for account activation.
Sincerely,
The Consultation Service Team
`,
};
try {
if (EMAIL_USER !== '' && transporter) {
await transporter.sendMail(welcomeMailOptions);
writeLog(`Sent welcome email to ${email}`);
}
} catch (emailError) {
writeLog('Error sending welcome email:', emailError);
}
res.json({ message: 'Email verified successfully' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Email verification error:', error);
res.status(500).json({ error: 'Email verification failed' });
} finally {
if (connection) connection.release();
}
});
// Middleware to verify Client JWT
const verifyClientToken = (req, res, next) => {
const token = req.cookies?.token;
if (token) {
jwt.verify(token, JWT_SECRET, (err, decoded) => {
if (err) {
writeLog('[verifyClientToken] JWT verification error:', err.name, err.message);
return res.status(403).json({ error: `Forbidden: Token verification failed (${err.name})` });
}
if (!decoded || !decoded.userId || !decoded.roles) {
writeLog('[verifyClientToken] Verification failed. Decoded payload:', decoded, 'userId/clientId or roles missing.');
return res.status(403).json({ error: 'Forbidden: Invalid token payload' });
}
// For backward compatibility with other routes in this file, create req.client
req.client = {
...decoded,
clientId: decoded.userId
};
req.userRoles = decoded.roles;
next();
});
} else {
writeLog('[verifyClientToken] Unauthorized: Missing client token in headers.');
res.status(401).json({ error: 'Unauthorized: Missing client token' });
}
};
// API endpoint for client to get their own details
router.get('/api/client/me', verifyClientToken, async (req, res) => {
try {
const [users] = await executeSql(db,
'SELECT user_id as client_id, first_name, last_name, email FROM users_v2 WHERE user_id = ?',
[req.client.clientId]
);
if (users.length === 0) {
return res.status(404).json({ error: 'Client not found' });
}
res.json(users[0]);
} catch (error) {
writeLog('Error fetching client details for /api/client/me:', error);
res.status(500).json({ error: 'Failed to fetch client details' });
}
});
// API endpoint for client to delete their own account (Right to Erasure)
router.delete('/api/client/me', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId; // Get clientId from verified token
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// 1. Get all consultation IDs for the client
const [consultations] = await executeSql(connection,
'SELECT client_consultation_id FROM client_consultations WHERE user_id = ?',
[clientId]
);
const consultationIds = consultations.map(c => c.client_consultation_id);
if (consultationIds.length > 0) {
// 2. Get all food plan IDs for these consultations
const [foodPlans] = await executeSql(connection,
`SELECT plan_id FROM client_food_plans WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
const foodPlanIds = foodPlans.map(fp => fp.plan_id);
// 3. Delete hourly food plan details
if (foodPlanIds.length > 0) {
await executeSql(connection,
`DELETE FROM client_food_plan_hourly_details WHERE plan_id IN (${foodPlanIds.map(() => '?').join(',')})`,
foodPlanIds
);
}
// 4. Delete client food plans
await executeSql(connection,
`DELETE FROM client_food_plans WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
// 5. Get all medical history IDs for these consultations
const [medicalHistories] = await executeSql(connection,
`SELECT history_id FROM client_medical_history WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
const historyIds = medicalHistories.map(mh => mh.history_id);
// 5.1 Get all blood test report IDs for these consultations
const [bloodReports] = await executeSql(connection,
`SELECT report_id FROM client_blood_test_reports WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
const reportIds = bloodReports.map(br => br.report_id);
// 5.2 Delete blood test results
if (reportIds.length > 0) {
await executeSql(connection,
`DELETE FROM client_blood_test_results WHERE report_id IN (${reportIds.map(() => '?').join(',')})`,
reportIds
);
}
// 6. Delete client medications
if (historyIds.length > 0) {
await executeSql(connection,
`DELETE FROM client_medications WHERE history_id IN (${historyIds.map(() => '?').join(',')})`,
historyIds
);
}
// 7. Delete client medical history
await executeSql(connection,
`DELETE FROM client_medical_history WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
// 7.1 Delete client blood test reports
await executeSql(connection,
`DELETE FROM client_blood_test_reports WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
}
// 8. Delete client consultations
await executeSql(connection, 'DELETE FROM client_consultations WHERE user_id = ?', [clientId]);
// 9. Delete user roles
await executeSql(connection, 'DELETE FROM user_roles WHERE user_id = ?', [clientId]); // Still hard delete roles
// 10. Hard delete user from users_v2 table (Right to Erasure)
await executeSql(connection, 'DELETE FROM users_v2 WHERE user_id = ?', [clientId]);
await connection.commit();
res.json({ message: 'Account and all associated data deleted successfully.' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error deleting client account:', error);
res.status(500).json({ error: 'Failed to delete account.' });
} finally {
if (connection) connection.release();
}
});
const childTables = {
'client_blood_test_reports': 'client_blood_test_results/report_id/result_id/test_code=value',
'client_medical_history': 'client_medications/history_id/medication_id/_index',
'client_food_plans': 'client_food_plan_hourly_details/plan_id/detail_id/time_slot=present_intake,proposed_structure,additional_points'
}
router.get('/api/client/compare/:clientId', verifyClientToken, async (req, res) => {
let { clientId } = req.params;
const staffId = req.user?.userId || req.client?.userId;
if (clientId === 'me') {
clientId = req.client.clientId;
} else if (req.userRoles.includes('admin')) {
// allowed
} else if (!await isStaffAuthorizedForClient(db, staffId, req.userRoles, clientId)) {
res.status(403).json({ error: 'Forbidden: You are not authorized to view other client\'s data.' });
return;
}
writeLog(`[${new Date().toISOString()}] /api/client/compare/${clientId} HIT`);
try {
const [consultations] = await executeSql(db,
`SELECT * FROM client_consultations WHERE user_id = ? ORDER BY created_at`,
[clientId]
);
let fieldSet = new Set();
for (let consultation of consultations) {
for (let [table, subTable] of Object.entries(childTables)) {
const [children] = await executeSql(db,
// Fetch the most RECENT record for the consultation to ensure
// the latest data (e.g., from an admin update) is shown.
`SELECT * FROM ${table} WHERE client_consultation_id = ? ORDER BY created_at DESC LIMIT 1`,
[consultation.client_consultation_id]
);
let [name, key, subKey, matchFields] = subTable.split('/');
let [f, v] = matchFields.split('=');
matchFields = f.split(',');
for (let child of children) {
let fk = child[key];
delete child[key];
if (fk) {
const [grandChildren] = await executeSql(db,
`SELECT * FROM ${name} WHERE ${key} = ?`,
[fk]
);
let flattened = {};
let index = 1;
for (let grandChild of grandChildren) {
let matchValues = [];
for (let field of matchFields) {
if (field === '_index') {
matchValues.push(index);
} else {
matchValues.push(grandChild[field]);
}
}
if (v) {
for (let field of v.split(',')) {
flattened[name.replace('client_', '') + '_' + field + '_' + matchValues.join(', ')] = grandChild[field];
}
} else {
flattened[name.replace('client_', '') + '_' + matchValues.join(', ')] = Object.entries(grandChild).filter(([k, v]) => k !== key && k !== subKey).map(([k, v]) => `${v}`).join(', ');
}
delete grandChild[key];
delete grandChild[subKey];
index++;
}
Object.assign(child, flattened);
} else {
child[name] = [];
}
delete child['client_consultation_id'];
}
if (children.length > 0) {
// When processing blood test reports, rename 'report_date' to match what the frontend expects.
// This ensures the correct date is used for the column header in the comparison view.
if (table === 'client_blood_test_reports' && children[0].report_date) {
children[0].blood_test_results_report_date = children[0].report_date;
delete children[0].report_date;
}
Object.assign(consultation, children[0]);
for (let name of Object.keys(children[0])) {
fieldSet.add(name);
}
}
}
}
for (let consultation of consultations) {
for (let field of fieldSet) {
if (!(field in consultation)) {
consultation[field] = '';
}
}
for (let field of Object.keys(consultation)) {
if (consultation[field] === null || typeof consultation[field] === 'undefined') {
consultation[field] = '';
} else if (consultation[field] instanceof Date) {
consultation[field] = JSON.stringify(consultation[field]);
} else {
consultation[field] = `${consultation[field]}`;
}
}
}
res.json(consultations);
} catch (error) {
writeLog('Error fetching client consultations for admin:', error);
res.status(500).json({ error: 'Failed to fetch client consultations' });
}
});
// API endpoint for client to save/update their personal details
router.post('/api/client/personal-details', verifyClientToken, async (req, res) => {
writeLog(`[${new Date().toISOString()}] HIT: POST /api/client/personal-details`); // <-- ADD THIS LINE
const clientId = req.client.clientId; // Get clientId from verified token
const {
first_name, last_name, mobile_number, email, // Core details, might also be updatable here
height_cms, weight_kg, age_years, gender, marital_status, address_1, address_2, address_3, city, pincode, shift_duty, joint_family, is_vegetarian, is_vegan, is_jain, has_lactose_intolerance, date_of_payment: raw_date_of_payment, reference_source,
// health_executive_id, // This is enrolled_by_executive_id, handle separately if needed or via admin
health_issues, food_liking, food_disliking, job_description, job_timings, sedentary_status, travelling_frequency
} = req.body;
// Basic validation (you can add more specific validation as needed)
if (!first_name || !last_name || !email || !mobile_number) {
return res.status(400).json({ error: 'Basic contact information (name, email, mobile) is required.' });
}
// Handle date_of_payment: convert empty string to null
const date_of_payment = (raw_date_of_payment === '' || raw_date_of_payment === undefined) ? null : raw_date_of_payment;
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// Check if the new email (if changed) already exists for another user
const [existingUsers] = await executeSql(connection,
'SELECT user_id FROM users_v2 WHERE email = ? AND user_id != ?',
[email, clientId]
);
if (existingUsers.length > 0) {
await connection.rollback();
return res.status(409).json({ error: 'Email already registered for another user.' });
}
// 1. Update the new users_v2 table
await executeSql(connection, `
UPDATE users_v2 SET
first_name = ?, last_name = ?, mobile_number = ?, email = ?,
address_1 = ?, address_2 = ?, address_3 = ?, city = ?, pincode = ?,
reference_source = ?, updated_at = CURRENT_TIMESTAMP
WHERE user_id = ?`,
[
first_name, last_name, mobile_number, email,
address_1, address_2, address_3, city, pincode,
reference_source, clientId
]
);
// 3. Update the latest client_consultations record
const [result] = await executeSql(connection, `
UPDATE client_consultations SET
height_cms = ?, weight_kg = ?, age_years = ?, gender = ?, marital_status = ?,
shift_duty = ?, joint_family = ?, is_vegetarian = ?, is_vegan = ?, is_jain = ?,
has_lactose_intolerance = ?, date_of_payment = ?,
health_issues = ?, food_liking = ?, food_disliking = ?,
job_description = ?, job_timings = ?, sedentary_status = ?, travelling_frequency = ?,
updated_at = CURRENT_TIMESTAMP
WHERE user_id = ? AND is_latest = TRUE`,
[
height_cms || null, weight_kg || null, age_years || null, gender || null, marital_status || null,
shift_duty || null, joint_family || null, is_vegetarian || null, is_vegan || null, is_jain || null,
has_lactose_intolerance || null, date_of_payment, // date_of_payment is already handled
health_issues || null, food_liking || null, food_disliking || null,
job_description || null, job_timings || null, sedentary_status || null, travelling_frequency || null,
clientId
]
);
if (result.affectedRows === 0) {
writeLog(`Warning: Client ${clientId} updated personal details, but no 'latest' consultation record was found to update.`);
}
await connection.commit();
res.json({ message: 'Personal details updated successfully.' });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error updating client personal details:', error);
res.status(500).json({ error: 'Failed to update personal details.' });
} finally {
if (connection) connection.release();
}
});
// API endpoint for client to GET their own full personal details
router.get('/api/client/me/personal-details', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId;
try {
const [clients] = await executeSql(db, `
SELECT
u.first_name, u.last_name, u.mobile_number, u.email,
u.address_1, u.address_2, u.address_3, u.city, u.pincode,
u.reference_source,
u.assigned_executive_id as enrolled_by_executive_id,
u.assigned_nutritionist_id as nutritionist_id,
CONCAT(exec.first_name, ' ', exec.last_name) as executive_name,
cc.height_cms, cc.weight_kg, cc.age_years, cc.gender, cc.marital_status,
cc.shift_duty, cc.joint_family, cc.is_vegetarian, cc.is_vegan, cc.is_jain,
cc.has_lactose_intolerance, cc.date_of_payment,
cc.health_issues, cc.food_liking, cc.food_disliking,
cc.job_description, cc.job_timings, cc.sedentary_status, cc.travelling_frequency
FROM users_v2 u
LEFT JOIN users_v2 exec ON u.assigned_executive_id = exec.user_id
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
WHERE u.user_id = ?`,
[clientId]
);
if (clients.length === 0) {
return res.status(404).json({ error: 'Client not found' });
}
res.json(clients[0]); // Send all fetched details
} catch (error) {
writeLog('Error fetching client personal details for /api/client/me/personal-details:', error);
res.status(500).json({ error: 'Failed to fetch your personal details' });
}
});
// API endpoint for client to save their blood test results
router.post('/api/client/blood-tests', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId;
const formData = req.body; // This is the raw data from the client
writeLog(`[${new Date().toISOString()}] /api/client/blood-tests HIT for clientId: ${clientId}`);
writeLog('Received blood test formData:', JSON.stringify(formData, null, 2));
// Extract report dates, convert empty strings to null
const report_date = formData.report_date;
writeLog('Parsed report dates:', { report_date });
const connection = await db.getConnection(); // Get a connection from the pool for transaction
try {
await connection.beginTransaction();
writeLog('Transaction started.');
// Find the latest consultation for the client using the new user_id
const [consultations] = await executeSql(connection,
`SELECT client_consultation_id FROM client_consultations WHERE user_id = ? AND is_latest = TRUE`,
[clientId]
);
if (consultations.length === 0) {
throw new Error('No active consultation found for this client to save the blood test results against.');
}
const consultationId = consultations[0].client_consultation_id;
// 0. remove old reports for this specific consultation
await executeSql(connection,
`DELETE FROM client_blood_test_reports WHERE client_consultation_id = ?`,
[consultationId]
);
// 1. Insert into client_blood_test_reports
const [reportResult] = await executeSql(connection,
`INSERT INTO client_blood_test_reports (client_consultation_id, report_date)
VALUES (?, ?)`,
[consultationId, report_date]
);
const reportId = reportResult.insertId;
writeLog('Inserted into client_blood_test_reports, reportId:', reportId);
// 2. Prepare and insert into client_blood_test_results
const testResults = [];
const testData = {}; // To group values by test_code
for (const key in formData) {
if (key.startsWith('report_date')) continue; // Skip already processed report dates
const testCode = key;
testData[testCode] = { value: formData[key] === '' ? null : formData[key] };
}
writeLog('Processed testData object:', JSON.stringify(testData, null, 2));
for (const testCode in testData) {
if (Object.values(testData[testCode]).some(val => val !== null)) { // Only insert if there's at least one value
testResults.push([
reportId,
testCode,
testData[testCode].value
]);
}
}
writeLog('Prepared testResults for bulk insert (first 5 rows if many):', JSON.stringify(testResults.slice(0, 5), null, 2));
writeLog('Total testResult rows to insert:', testResults.length);
if (testResults.length > 0) {
const [resultsInsertResult] = await connection.query(
'INSERT INTO client_blood_test_results (report_id, test_code, value) VALUES ?',
[testResults] // Bulk insert
);
writeLog('Bulk insert into client_blood_test_results result:', resultsInsertResult);
} else {
writeLog('No test results to insert into client_blood_test_results.');
}
// Update the consultation's timestamp to signal a change to the frontend.
await executeSql(connection,
`UPDATE client_consultations SET updated_at = CURRENT_TIMESTAMP WHERE client_consultation_id = ?`,
[consultationId]
);
writeLog(`Updated consultation timestamp for blood test submission for client ${clientId}`);
await connection.commit();
writeLog('Transaction committed successfully.');
res.json({ message: 'Blood test results saved successfully.', reportId: reportId });
} catch (error) {
if (connection) await connection.rollback(); // Ensure connection exists before rollback
writeLog('Error saving blood test results (ROLLBACK EXECUTED):', error);
res.status(500).json({ error: 'Failed to save blood test results.' });
} finally {
if (connection) connection.release();
}
});
// API endpoint for client to GET their latest blood test results
router.get('/api/client/blood-tests/latest', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId;
writeLog(`[${new Date().toISOString()}] /api/client/blood-tests/latest HIT for clientId: ${clientId}`);
try {
// 1. Get the latest report_id for the client
const [latestReportMeta] = await executeSql(db, `
SELECT cbtr.report_id, cbtr.report_date
FROM client_blood_test_reports cbtr
JOIN client_consultations cc ON cbtr.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ? AND cc.is_latest = 1
ORDER BY cbtr.created_at DESC
LIMIT 1`,
[clientId]
);
if (latestReportMeta.length === 0) {
return res.json({ message: 'No blood test reports found for this client.' }); // Not an error, just no data
}
const report = latestReportMeta[0];
const reportId = report.report_id;
// 2. Get all results for that report_id
const [results] = await executeSql(db,
`SELECT test_code, value
FROM client_blood_test_results
WHERE report_id = ?`,
[reportId]
);
// Combine report dates and results into a single response object
const fullReportData = {
...report, // Includes report_id and the 5 report_date_N fields
results: results // Array of test results
};
res.json(fullReportData);
} catch (error) {
writeLog('Error fetching latest blood test results:', error);
res.status(500).json({ error: 'Failed to fetch latest blood test results.' });
}
});
// API endpoint for client to save their food plan
router.post('/api/client/food-plan', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId;
const { hourly_plan } = req.body;
// Sanitize the HTML content to prevent XSS attacks, then ensure it's null if empty
const sanitizedRecommendations = sanitizeHtml(req.body.additional_personal_recommendations || '', {
allowedTags: [ 'p', 'b', 'i', 'em', 'strong', 'ul', 'ol', 'li', 'br', 'h1', 'h2', 'h3' ],
allowedAttributes: {} // No attributes allowed
});
writeLog(`[${new Date().toISOString()}] /api/client/food-plan HIT for clientId: ${clientId}`);
writeLog('Received food plan data:', JSON.stringify(req.body, null, 2));
if (!hourly_plan || typeof hourly_plan !== 'object') {
return res.status(400).json({ error: 'Hourly plan data is missing or invalid.' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
writeLog('Food plan save: Transaction started.');
// Find the latest consultation for the client using the new user_id
const [consultations] = await executeSql(connection,
`SELECT client_consultation_id FROM client_consultations WHERE user_id = ? AND is_latest = TRUE`,
[clientId]
);
if (consultations.length === 0) {
throw new Error('No active consultation found for this client to save the food plan against.');
}
const consultationId = consultations[0].client_consultation_id;
// 0. remove old client_food_plans for this specific consultation
await executeSql(connection,
`DELETE FROM client_food_plans WHERE client_consultation_id = ?`,
[consultationId]
);
writeLog(`Food plan save: Deleted existing plans for consultation_id: ${consultationId}`);
// Step 2: Insert the new food plan into client_food_plans
const [planResult] = await executeSql(connection, `
INSERT INTO client_food_plans (client_consultation_id, additional_personal_recommendations)
VALUES (?, ?)`,
[consultationId, sanitizedRecommendations || null]
);
const planId = planResult.insertId;
writeLog(`Food plan save: Inserted into client_food_plans, planId: ${planId}`);
// Step 3: Prepare and insert hourly details
const hourlyDetailsToInsert = [];
for (const timeSlot in hourly_plan) {
if (hourly_plan.hasOwnProperty(timeSlot)) {
const slotData = hourly_plan[timeSlot];
// Only insert if at least one field for the time slot has data
if (slotData.present_intake || slotData.proposed_structure || slotData.additional_points) {
hourlyDetailsToInsert.push([
planId,
timeSlot, // e.g., "06:00"
slotData.present_intake || null,
slotData.proposed_structure || null,
slotData.additional_points || null
]);
}
}
}
if (hourlyDetailsToInsert.length > 0) {
await connection.query(
'INSERT INTO client_food_plan_hourly_details (plan_id, time_slot, present_intake, proposed_structure, additional_points) VALUES ?',
[hourlyDetailsToInsert] // Bulk insert
);
writeLog(`Food plan save: Inserted ${hourlyDetailsToInsert.length} hourly details for planId: ${planId}`);
} else {
writeLog(`Food plan save: No hourly details to insert for planId: ${planId}`);
}
// Update the consultation's timestamp to signal a change to the frontend.
await executeSql(connection,
`UPDATE client_consultations SET updated_at = CURRENT_TIMESTAMP WHERE client_consultation_id = ?`,
[consultationId]
);
writeLog(`Updated consultation timestamp for food plan submission for client ${clientId}`);
await connection.commit();
writeLog('Food plan save: Transaction committed successfully.');
res.json({ message: 'Food plan saved successfully.', planId: planId });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error saving food plan (ROLLBACK EXECUTED):', error);
res.status(500).json({ error: 'Failed to save food plan.' });
} finally {
if (connection) connection.release();
}
});
// API endpoint for client to GET their own latest food plan
router.get('/api/client/food-plan/latest', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId; // Get clientId from verified token
writeLog(`[${new Date().toISOString()}] CLIENT ${clientId} /api/client/food-plan/latest HIT`);
try {
// 1. Get the latest plan_id for the client
// Ensure we get the one marked as is_latest = TRUE, and order by updated_at to be sure.
const [latestPlanMeta] = await executeSql(db, `
SELECT cfp.plan_id, cfp.additional_personal_recommendations, cfp.created_at, cfp.updated_at
FROM client_food_plans cfp
JOIN client_consultations cc ON cfp.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ? AND cc.is_latest = 1
ORDER BY cfp.updated_at DESC
LIMIT 1`,
[clientId]
);
if (latestPlanMeta.length === 0) {
// It's not an error if no plan is found, just means the client doesn't have one yet.
return res.json({ message: 'No food plan found for this client.' });
}
const plan = latestPlanMeta[0];
const planId = plan.plan_id;
// 2. Get all hourly details for that plan_id
const [hourlyDetails] = await executeSql(db,
`SELECT time_slot, present_intake, proposed_structure, additional_points
FROM client_food_plan_hourly_details
WHERE plan_id = ?
ORDER BY time_slot ASC`, // Ensure consistent order
[planId]
);
res.json({ ...plan, hourly_details: hourlyDetails });
} catch (error) {
writeLog(`Error fetching latest food plan for client ${clientId}:`, error);
res.status(500).json({ error: 'Failed to fetch your latest food plan.' });
}
});
// API endpoint for clients to GET general food recommendations
router.post('/api/client/medical-history', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId;
const { family_medical_history, medications, is_final_submission } = req.body;
const sanitizedFamilyHistory = sanitizeHtml(family_medical_history, {
allowedTags: [],
allowedAttributes: {}
});
writeLog(`[${new Date().toISOString()}] /api/client/medical-history HIT for clientId: ${clientId}`);
writeLog(`Value of is_final_submission from request body: ${is_final_submission} (Type: ${typeof is_final_submission})`);
writeLog('Received medical history data:', JSON.stringify(req.body, null, 2));
if ( typeof family_medical_history === 'undefined') {
return res.status(400).json({ error: ' family medical history fields are required, even if empty.' });
}
const connection = await db.getConnection();
try {
await connection.beginTransaction();
writeLog('Medical history save: Transaction started.');
// Find the latest consultation for the client using the new user_id
const [consultations] = await executeSql(connection,
`SELECT client_consultation_id FROM client_consultations WHERE user_id = ? AND is_latest = TRUE`,
[clientId]
);
if (consultations.length === 0) {
throw new Error('No active consultation found for this client to save the medical history against.');
}
const consultationId = consultations[0].client_consultation_id;
// 0. remove old medical history for this specific consultation
await executeSql(connection,
`DELETE FROM client_medical_history WHERE client_consultation_id = ?`,
[consultationId]
);
// Step 1: Insert the new medical history into client_medical_history
const [historyResult] = await executeSql(connection, `
INSERT INTO client_medical_history (client_consultation_id, family_medical_history)
VALUES (?, ?)`,
[consultationId, sanitizedFamilyHistory]
);
const historyId = historyResult.insertId;
writeLog(`Medical history save: Inserted into client_medical_history, historyId: ${historyId}`);
const updateQuery = `
UPDATE client_consultations SET updated_at = CURRENT_TIMESTAMP ${is_final_submission ? ', is_finalized = TRUE' : ''}
WHERE client_consultation_id = ?
${is_final_submission ? ' AND (is_finalized = FALSE OR is_finalized IS NULL)' : ''}
`;
const [updateResult] = await executeSql(connection, updateQuery, [consultationId]);
if (updateResult.affectedRows > 0) {
if (is_final_submission) {
writeLog(`Final Submission set to TRUE and timestamp updated for consultation ${consultationId}`);
} else {
writeLog(`Timestamp updated for draft medical history for consultation ${consultationId}`);
}
} else {
if (is_final_submission) {
writeLog(`Final submission for consultation ${consultationId} was already set or consultation not found. No update made.`);
} else {
writeLog(`Consultation timestamp for consultation ${consultationId} not updated (no record found or already up-to-date).`);
}
}
// Step 2: Prepare and insert medications, if any
if (medications && Array.isArray(medications) && medications.length > 0) {
const medicationsToInsert = medications.map(med => [
historyId,
med.diagnosis || null,
med.medicine_name || null,
med.power || null,
med.timing || null,
med.since_when || null
]);
if (medicationsToInsert.length > 0) {
await connection.query(
'INSERT INTO client_medications (history_id, diagnosis, medicine_name, power, timing, since_when) VALUES ?',
[medicationsToInsert] // Bulk insert
);
writeLog(`Medical history save: Inserted ${medicationsToInsert.length} medications for historyId: ${historyId}`);
}
} else {
writeLog(`Medical history save: No medications to insert for historyId: ${historyId}`);
}
await connection.commit();
writeLog('Medical history save: Transaction committed successfully.');
res.json({ message: `Medical history ${is_final_submission ? 'submitted' : 'saved'} successfully.`, historyId: historyId });
} catch (error) {
if (connection) await connection.rollback();
writeLog('Error saving medical history (ROLLBACK EXECUTED):', error);
res.status(500).json({ error: 'Failed to save medical history.' });
} finally {
if (connection) connection.release();
}
});
// API endpoint for client to GET their latest medical history
router.get('/api/client/medical-history/latest', verifyClientToken, async (req, res) => {
const clientId = req.client.clientId;
writeLog(`[${new Date().toISOString()}] /api/client/medical-history/latest HIT for clientId: ${clientId}`);
try {
// 1. Get the latest history_id for the client
const [latestHistoryMeta] = await executeSql(db, `
SELECT cmh.history_id, cmh.family_medical_history, cc.is_finalized, cmh.created_at, cmh.updated_at
FROM client_medical_history cmh
JOIN client_consultations cc ON cmh.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ? AND cc.is_latest = 1
ORDER BY cmh.updated_at DESC
LIMIT 1`,
[clientId]
);
if (latestHistoryMeta.length === 0) {
return res.json({ message: 'No medical history found for this client.' });
}
const history = latestHistoryMeta[0];
const historyId = history.history_id;
// 2. Get all medications for that history_id
const [medications] = await executeSql(db,
`SELECT medication_id, diagnosis, medicine_name, power, timing, since_when
FROM client_medications
WHERE history_id = ?`,
[historyId]
);
res.json({ ...history, medications: medications });
} catch (error) {
writeLog('Error fetching latest medical history:', error);
res.status(500).json({ error: 'Failed to fetch latest medical history.' });
}
});
// Admin: Get latest food plan for a specific client
}
--- staffRoutes.mjs ---
import bcrypt from 'bcrypt';
import crypto from 'crypto';
import jwt from 'jsonwebtoken';
import { writeLog } from './common/utils.mjs';
import { checkPasswordPolicy } from './common/utils.mjs';
import { executeSql } from './common/database.mjs';
export function setupStaffRoutes({ router, db, JWT_SECRET, EMAIL_USER, transporter, appUrl }) {
// Middleware to verify Staff (Nutritionist/Executive) JWT
const verifyStaffToken = (req, res, next) => {
const token = req.cookies?.token;
if (token) {
jwt.verify(token, JWT_SECRET, (err, decoded) => {
if (err) {
return res.status(403).json({ error: 'Forbidden: Invalid token' });
}
// Handle both new 'roles' array and old 'role' string for backward compatibility
let userRoles = [];
if (decoded.roles && Array.isArray(decoded.roles)) {
userRoles = decoded.roles;
} else if (typeof decoded.role === 'string') {
userRoles = [decoded.role];
}
if (userRoles.length === 0) {
return res.status(403).json({ error: 'Forbidden: No roles found in token' });
}
const hasStaffRole = userRoles.includes('nutritionist') || userRoles.includes('executive');
if (!hasStaffRole) {
return res.status(403).json({ error: 'Forbidden: User does not have a valid staff role.' });
}
// For consistency, ensure req.user.roles is always an array
req.user = { ...decoded, roles: userRoles };
next();
});
} else {
res.status(401).json({ error: 'Unauthorized: Missing staff token' });
}
};
// DEBUG: Check if server code is updating
router.get('/api/staff/version', (req, res) => {
res.json({ version: 'staging-update-check-v1', timestamp: new Date().toISOString() });
});
// Serve nutritionist dashboard, protected by staff token
router.get('/nutritionist-dashboard.html', (req, res) => {
res.sendFile(__dirname + '/public/nutritionist-dashboard.html');
});
// Serve executive dashboard, protected by staff token
router.get('/executive-dashboard.html', (req, res) => {
res.sendFile(__dirname + '/public/executive-dashboard.html');
});
// API endpoint for a nutritionist to get their assigned clients
router.get('/api/nutritionist/my-clients', verifyStaffToken, async (req, res) => {
if (!req.user.roles.includes('nutritionist')) {
return res.status(403).json({ error: 'Forbidden: Access denied for this role' });
}
const nutritionistId = req.user.userId;
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id, u.first_name, u.last_name, u.email, u.mobile_number
FROM users_v2 u
WHERE u.assigned_nutritionist_id = ? AND u.is_active = TRUE
ORDER BY u.last_name, u.first_name
`,
[nutritionistId]
);
res.json(clients);
} catch (error) {
writeLog('Error fetching nutritionist clients:', error);
res.status(500).json({ error: 'Failed to fetch assigned clients' });
}
});
// API endpoint for a nutritionist to search their assigned clients
router.get('/api/nutritionist/my-clients/search', verifyStaffToken, async (req, res) => {
if (!req.user.roles.includes('nutritionist')) {
return res.status(403).json({ error: 'Forbidden: Access denied for this role' });
}
const nutritionistId = req.user.userId;
writeLog(`[${new Date().toISOString()}] NUTRITIONIST ${nutritionistId} /api/nutritionist/my-clients/search HIT with query:`, req.query);
try {
let sql = `
SELECT
u.user_id as client_id, u.first_name, u.last_name, u.email, u.mobile_number
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
WHERE r.role_name = 'client' AND u.is_active = TRUE`;
const params = [];
if (req.query.client_id) {
sql += " AND u.user_id = ?";
params.push(req.query.client_id);
}
if (req.query.first_name) {
sql += " AND u.first_name LIKE ?";
params.push(`%${req.query.first_name}%`);
}
if (req.query.last_name) {
sql += " AND u.last_name LIKE ?";
params.push(`%${req.query.last_name}%`);
}
if (req.query.email) {
sql += " AND u.email LIKE ?";
params.push(`%${req.query.email}%`);
}
sql += " ORDER BY u.last_name, u.first_name";
const [clients] = await executeSql(db, sql, params);
res.json(clients);
} catch (error) {
writeLog('Error searching nutritionist clients:', error);
res.status(500).json({ error: 'Failed to search assigned clients' });
}
});
// API endpoint for an executive to get their enrolled clients
router.get('/api/executive/my-clients', verifyStaffToken, async (req, res) => {
if (!req.user.roles.includes('executive')) {
return res.status(403).json({ error: 'Forbidden: Access denied for this role' });
}
const executiveId = req.user.userId;
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number, u.created_at as registration_date
FROM users_v2 u
WHERE u.assigned_executive_id = ? AND u.is_active = TRUE
ORDER BY u.created_at DESC, u.last_name, u.first_name
`,
[executiveId]
);
res.json(clients);
} catch (error) {
writeLog('Error fetching executive clients:', error);
res.status(500).json({ error: 'Failed to fetch enrolled clients' });
}
});
// API endpoint for an executive to search their enrolled clients
router.get('/api/executive/my-clients/search', verifyStaffToken, async (req, res) => {
if (!req.user.roles.includes('executive')) {
return res.status(403).json({ error: 'Forbidden: Access denied for this role' });
}
const executiveId = req.user.userId;
writeLog(`[${new Date().toISOString()}] EXECUTIVE ${executiveId} /api/executive/my-clients/search HIT with query:`, req.query);
try {
let sql = `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.email, u.mobile_number, u.created_at as registration_date
FROM users_v2 u
WHERE u.assigned_executive_id = ? AND u.is_active = TRUE`;
const params = [executiveId];
if (req.query.client_id) {
sql += " AND u.user_id = ?";
params.push(req.query.client_id);
}
if (req.query.first_name) {
sql += " AND u.first_name LIKE ?";
params.push(`%${req.query.first_name}%`);
}
if (req.query.last_name) {
sql += " AND u.last_name LIKE ?";
params.push(`%${req.query.last_name}%`);
}
if (req.query.email) {
sql += " AND u.email LIKE ?";
params.push(`%${req.query.email}%`);
}
sql += " ORDER BY u.created_at DESC, u.last_name, u.first_name";
const [clients] = await executeSql(db, sql, params);
res.json(clients);
} catch (error) {
writeLog('Error searching executive clients:', error);
res.status(500).json({ error: 'Failed to search enrolled clients' });
}
});
// --- Admin Client Management Routes ---
// Get all clients for Admin
router.get('/api/staff/list/nutritionists', async (req, res) => {
writeLog(`[${new Date().toISOString()}] Request received for /api/staff/list/nutritionists`); // Adjusted log
try {
const [nutritionists] = await executeSql(db, `
SELECT u.user_id, u.first_name, u.last_name
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
WHERE r.role_name = 'nutritionist' AND u.is_active = TRUE
ORDER BY u.last_name, u.first_name`
);
res.json(nutritionists);
} catch (error) {
writeLog('Error fetching list of nutritionists:', error);
res.status(500).json({ error: 'Failed to fetch nutritionists list.' });
}
});
// API to list active executives for client selection
router.get('/api/staff/list/executives', async (req, res) => {
writeLog(`[${new Date().toISOString()}] Request received for /api/staff/list/executives`);
try {
const [executives] = await executeSql(db, `
SELECT u.user_id, u.first_name, u.last_name
FROM users_v2 u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
WHERE r.role_name = 'executive' AND u.is_active = TRUE
ORDER BY u.last_name, u.first_name`
);
res.json(executives);
} catch (error) {
writeLog('Error fetching list of executives:', error);
res.status(500).json({ error: 'Failed to fetch executives list.' });
}
});
// API for client to update their staff preferences
// This endpoint should be protected by client authentication (once client login is implemented)
// For now, let's assume client_id is passed in the body for simplicity,
// but in a real app, it would come from the client's JWT.
router.get('/api/general-food-recommendations', async (req, res) => {
writeLog(`[${new Date().toISOString()}] /api/general-food-recommendations HIT`);
try {
// Fetch the most recent (or only) general recommendation
// Assuming we'll mostly have one row that gets updated, or we take the latest if multiple exist.
const [rows] = await executeSql(db,
'SELECT recommendations_text FROM general_food_recommendations ORDER BY updated_at DESC LIMIT 1'
);
if (rows.length > 0) {
res.json({ recommendations: rows[0].recommendations_text });
} else {
res.json({ recommendations: 'No general recommendations are currently set.' });
}
} catch (error) {
writeLog('Error fetching general food recommendations:', error);
res.status(500).json({ error: 'Failed to fetch general food recommendations.' });
}
});
// API endpoint for ADMIN to SET/UPDATE general food recommendations
router.get('/api/staff/clients/:clientId/personal-details', verifyStaffToken, async (req, res) => {
const { clientId } = req.params;
const staffId = req.user.userId;
const staffRole = req.user.roles;
writeLog(`[${new Date().toISOString()}] STAFF (${staffRole} ${staffId}) /api/staff/clients/${clientId}/personal-details HIT`);
if (!await isStaffAuthorizedForClient(db, staffId, staffRole, clientId)) {
return res.status(403).json({ error: 'Forbidden: You are not authorized to view this client.' });
}
// Reuse admin logic for fetching
try {
const [clients] = await executeSql(db, `
SELECT
u.user_id as client_id,
u.first_name, u.last_name, u.mobile_number, u.email,
u.address_1, u.address_2, u.address_3, u.city, u.pincode,
u.reference_source,
u.assigned_executive_id as enrolled_by_executive_id,
u.assigned_nutritionist_id as nutritionist_id,
u.created_at as registration_date,
u.is_email_verified,
u.is_active as is_account_active,
cc.height_cms, cc.weight_kg, cc.age_years, cc.gender, cc.marital_status,
cc.shift_duty, cc.joint_family, cc.is_vegetarian, cc.is_vegan, cc.is_jain,
cc.has_lactose_intolerance, cc.date_of_payment,
cc.health_issues, cc.food_liking, cc.food_disliking,
cc.job_description, cc.job_timings, cc.sedentary_status, cc.travelling_frequency
FROM users_v2 u
LEFT JOIN client_consultations cc ON u.user_id = cc.user_id AND cc.is_latest = 1
WHERE u.user_id = ?`,
[clientId]
);
if (clients.length === 0) return res.status(404).json({ error: 'Client not found' });
res.json(clients[0]);
} catch (error) {
writeLog(`Error fetching personal details for client ${clientId} by staff:`, error);
res.status(500).json({ error: 'Failed to fetch client personal details.' });
}
});
// Staff: Get latest medical history for a specific client
router.get('/api/staff/clients/:clientId/medical-history/latest', verifyStaffToken, async (req, res) => {
const { clientId } = req.params;
const staffId = req.user.userId;
const staffRole = req.user.roles;
writeLog(`[${new Date().toISOString()}] STAFF (${staffRole} ${staffId}) /api/staff/clients/${clientId}/medical-history/latest HIT`);
if (!await isStaffAuthorizedForClient(db, staffId, staffRole, clientId)) {
return res.status(403).json({ error: 'Forbidden: You are not authorized to view this client.' });
}
try {
const [latestHistoryMeta] = await executeSql(db, `
SELECT cmh.history_id, cmh.family_medical_history, cmh.created_at, cmh.updated_at
FROM client_medical_history cmh
JOIN client_consultations cc ON cmh.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ?
AND cc.is_latest = 1
ORDER BY cmh.updated_at DESC
LIMIT 1`,
[clientId]
);
if (latestHistoryMeta.length === 0) return res.json({ message: 'No medical history found for this client.' });
const history = latestHistoryMeta[0];
const [medications] = await executeSql(db, `SELECT medication_id, diagnosis, medicine_name, power, timing, since_when FROM client_medications WHERE history_id = ?`, [history.history_id]);
res.json({ ...history, medications: medications });
} catch (error) {
writeLog(`Error fetching medical history for client ${clientId} by staff:`, error);
res.status(500).json({ error: 'Failed to fetch medical history.' });
}
});
// Staff: Get latest blood test results for a specific client
router.get('/api/staff/clients/:clientId/blood-tests/latest', verifyStaffToken, async (req, res) => {
const { clientId } = req.params;
const staffId = req.user.userId;
const staffRole = req.user.roles;
writeLog(`[${new Date().toISOString()}] STAFF (${staffRole} ${staffId}) /api/staff/clients/${clientId}/blood-tests/latest HIT`);
if (!await isStaffAuthorizedForClient(db, staffId, staffRole, clientId)) {
return res.status(403).json({ error: 'Forbidden: You are not authorized to view this client.' });
}
try {
const [latestReportMeta] = await executeSql(db, `
SELECT cbtr.report_id, cbtr.report_date, cbtr.created_at
FROM client_blood_test_reports cbtr
JOIN client_consultations cc ON cbtr.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ?
AND cc.is_latest = 1
ORDER BY cbtr.created_at DESC
LIMIT 1`,
[clientId]
);
if (latestReportMeta.length === 0) return res.json({ message: 'No blood test reports found for this client.' });
const report = latestReportMeta[0];
const [results] = await executeSql(db, `SELECT test_code, value FROM client_blood_test_results WHERE report_id = ?`, [report.report_id]);
res.json({ ...report, results: results });
} catch (error) {
writeLog(`Error fetching blood tests for client ${clientId} by staff:`, error);
res.status(500).json({ error: 'Failed to fetch blood tests.' });
}
});
// Staff: Get latest food plan for a specific client
router.get('/api/staff/clients/:clientId/food-plan/latest', verifyStaffToken, async (req, res) => {
const { clientId } = req.params;
const staffId = req.user.userId;
const staffRole = req.user.roles;
writeLog(`[${new Date().toISOString()}] STAFF (${staffRole} ${staffId}) /api/staff/clients/${clientId}/food-plan/latest HIT`);
if (!await isStaffAuthorizedForClient(db, staffId, staffRole, clientId)) {
return res.status(403).json({ error: 'Forbidden: You are not authorized to view this client.' });
}
try {
const [latestPlanMeta] = await executeSql(db, `
SELECT cfp.plan_id, cfp.additional_personal_recommendations, cfp.created_at, cfp.updated_at
FROM client_food_plans cfp
JOIN client_consultations cc ON cfp.client_consultation_id = cc.client_consultation_id
WHERE cc.user_id = ?
AND cc.is_latest = 1
ORDER BY cfp.updated_at DESC
LIMIT 1`,
[clientId]
);
if (latestPlanMeta.length === 0) return res.json({ message: 'No food plan found for this client.' });
const plan = latestPlanMeta[0];
const [hourlyDetails] = await executeSql(db, `SELECT time_slot, present_intake, proposed_structure, additional_points FROM client_food_plan_hourly_details WHERE plan_id = ? ORDER BY time_slot ASC`, [plan.plan_id]);
res.json({ ...plan, hourly_details: hourlyDetails });
} catch (error) {
writeLog(`Error fetching food plan for client ${clientId} by staff:`, error);
res.status(500).json({ error: 'Failed to fetch food plan.' });
}
});
// Nutritionist: Save/Update food plan for a specific client
router.post('/api/nutritionist/clients/:clientId/food-plan', verifyStaffToken, async (req, res) => {
const { clientId } = req.params;
const nutritionistId = req.user.userId;
const { hourly_plan, additional_personal_recommendations } = req.body;
if (!req.user.roles.includes('nutritionist')) {
return res.status(403).json({ error: 'Forbidden: Only nutritionists can save food plans.' });
}
writeLog(`[${new Date().toISOString()}] NUTRITIONIST ${nutritionistId} /api/nutritionist/clients/${clientId}/food-plan POST HIT`);
if (!await isStaffAuthorizedForClient(db, nutritionistId, 'nutritionist', clientId)) {
return res.status(403).json({ error: 'Forbidden: You are not assigned to this client.' });
}
if (!hourly_plan || typeof hourly_plan !== 'object') {
return res.status(400).json({ error: 'Hourly plan data is missing or invalid.' });
}
let connection;
try {
connection = await db.getConnection();
await connection.beginTransaction();
// Find the latest consultation for the client
const [consultations] = await connection.query(
`SELECT client_consultation_id FROM client_consultations
WHERE user_id = ? AND is_latest = TRUE`,
[clientId]
);
if (consultations.length === 0) {
throw new Error('No active consultation found for this client to save the food plan against.');
}
const consultationId = consultations[0].client_consultation_id;
// Fetch existing plan IDs to handle deletion explicitly
const [existingPlans] = await connection.query(
'SELECT plan_id FROM client_food_plans WHERE client_consultation_id = ?',
[consultationId]
);
if (existingPlans.length > 0) {
const planIds = existingPlans.map(p => p.plan_id);
// Delete children first (hourly details)
await connection.query(
'DELETE FROM client_food_plan_hourly_details WHERE plan_id IN (?)',
[planIds]
);
// Delete parents (plans)
await connection.query(
'DELETE FROM client_food_plans WHERE plan_id IN (?)',
[planIds]
);
}
// Insert the new food plan record
const [planResult] = await connection.query(
'INSERT INTO client_food_plans (client_consultation_id, additional_personal_recommendations, created_by_nutritionist_id) VALUES (?, ?, ?)',
[consultationId, additional_personal_recommendations || null, nutritionistId]
);
const planId = planResult.insertId;
// Prepare and insert hourly details
const hourlyDetailsToInsert = Object.entries(hourly_plan)
.map(([timeSlot, slotData]) => [
planId, timeSlot, slotData.present_intake || null,
slotData.proposed_structure || null, slotData.additional_points || null
])
.filter(detail => detail[2] || detail[3] || detail[4]);
if (hourlyDetailsToInsert.length > 0) {
await connection.query('INSERT INTO client_food_plan_hourly_details (plan_id, time_slot, present_intake, proposed_structure, additional_points) VALUES ?', [hourlyDetailsToInsert]);
}
await connection.commit();
res.json({ message: 'Client food plan updated successfully by nutritionist.', planId: planId });
} catch (error) {
if (connection) await connection.rollback();
writeLog(`Error saving food plan for client ${clientId} by nutritionist (ROLLBACK EXECUTED):`, error);
res.status(500).json({ error: `VERIFY_UPDATE: ${error.code || 'N/A'} - ${error.message}` });
} finally {
if (connection) connection.release();
}
});
}
// --- Staff (Nutritionist/Executive) Access to Client Details ---
// Helper function to check if staff is authorized for a client
export async function isStaffAuthorizedForClient(db, staffId, staffRole, clientId) {
const [clientRows] = await executeSql(db, `
SELECT assigned_nutritionist_id, assigned_executive_id
FROM users_v2
WHERE user_id = ?`,
[clientId]
);
if (clientRows.length === 0) {
return false; // Client not found
}
const client = clientRows[0];
if (staffRole.includes('nutritionist')) {
return true; // Nutritionists can view any client's data. Edit rights are handled separately.
}
if (staffRole.includes('executive') && client.assigned_executive_id === staffId) {
return true;
}
return false;
}
--- scripts/inactive_user_cleanup.mjs ---
import { executeSql, startDatabase } from '../common/database.mjs';
import { writeLog } from '../common/utils.mjs';
import nodemailer from 'nodemailer';
import dotenv from 'dotenv';
import path from 'path';
import { fileURLToPath } from 'url';
import fs from 'fs';
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
// Load environment variables
const envPath = path.join(__dirname, '..', '.env');
if (fs.existsSync(envPath)) {
const envConfig = dotenv.parse(fs.readFileSync(envPath));
for (const k in envConfig) { process.env[k] = envConfig[k]; }
}
export async function runCleanup() {
const db = await startDatabase({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
db: process.env.DB_DATABASE,
encryptionKey: process.env.ENC_KEY
});
const transporter = nodemailer.createTransport({
service: 'Gmail',
auth: {
user: process.env.EMAIL_USER,
pass: process.env.EMAIL_PASS,
},
});
try {
// 1. Find users inactive for > 24 months who haven't been warned yet
const [toWarn] = await executeSql(db,
`SELECT user_id, email, first_name FROM users_v2
WHERE last_login < DATE_SUB(NOW(), INTERVAL 24 MONTH)
AND deleted = 0 AND deletion_warning_sent_at IS NULL`
);
for (const user of toWarn) {
const mailOptions = {
from: `"Consultation Service" <${process.env.EMAIL_USER}>`,
to: user.email,
subject: 'Account Inactivity Warning',
html: `Dear ${user.first_name},
Your account has been inactive for over 2 years. It will be marked for deletion in 48 hours unless you log in now.
Regards,
Team 7.4
`
};
await transporter.sendMail(mailOptions);
await executeSql(db, 'UPDATE users_v2 SET deletion_warning_sent_at = NOW() WHERE user_id = ?', [user.user_id]);
writeLog(`[Cleanup] 48-hour warning email sent to ${user.email}`);
}
// 2. Permanent hard delete of users who were warned more than 48 hours ago
const [toDelete] = await executeSql(db,
`SELECT user_id, email FROM users_v2
WHERE deletion_warning_sent_at < DATE_SUB(NOW(), INTERVAL 48 HOUR)
AND deleted = 0`
);
for (const user of toDelete) {
const clientId = user.user_id;
const connection = await db.getConnection();
try {
await connection.beginTransaction();
// 1. Get all consultation IDs for the client
const [consultations] = await executeSql(connection,
'SELECT client_consultation_id FROM client_consultations WHERE user_id = ?',
[clientId]
);
const consultationIds = consultations.map(c => c.client_consultation_id);
if (consultationIds.length > 0) {
// 2. Get all food plan IDs
const [foodPlans] = await executeSql(connection,
`SELECT plan_id FROM client_food_plans WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`,
consultationIds
);
const foodPlanIds = foodPlans.map(fp => fp.plan_id);
// 3. Delete hourly food plan details
if (foodPlanIds.length > 0) {
await executeSql(connection,
`DELETE FROM client_food_plan_hourly_details WHERE plan_id IN (${foodPlanIds.map(() => '?').join(',')})`,
foodPlanIds
);
}
// 4. Delete client food plans
await executeSql(connection, `DELETE FROM client_food_plans WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`, consultationIds);
// 5. Get medical history and blood report IDs
const [medicalHistories] = await executeSql(connection, `SELECT history_id FROM client_medical_history WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`, consultationIds);
const historyIds = medicalHistories.map(mh => mh.history_id);
const [bloodReports] = await executeSql(connection, `SELECT report_id FROM client_blood_test_reports WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`, consultationIds);
const reportIds = bloodReports.map(br => br.report_id);
// 6. Delete blood test results
if (reportIds.length > 0) {
await executeSql(connection, `DELETE FROM client_blood_test_results WHERE report_id IN (${reportIds.map(() => '?').join(',')})`, reportIds);
}
// 7. Delete medications
if (historyIds.length > 0) {
await executeSql(connection, `DELETE FROM client_medications WHERE history_id IN (${historyIds.map(() => '?').join(',')})`, historyIds);
}
// 8. Delete medical history and blood reports
await executeSql(connection, `DELETE FROM client_medical_history WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`, consultationIds);
await executeSql(connection, `DELETE FROM client_blood_test_reports WHERE client_consultation_id IN (${consultationIds.map(() => '?').join(',')})`, consultationIds);
}
// 9. Delete consultations, roles, and user record
await executeSql(connection, 'DELETE FROM client_consultations WHERE user_id = ?', [clientId]);
await executeSql(connection, 'DELETE FROM user_roles WHERE user_id = ?', [clientId]);
await executeSql(connection, 'DELETE FROM users_v2 WHERE user_id = ?', [clientId]);
await connection.commit();
writeLog(`[Cleanup] User ${user.email} and all associated data permanently deleted due to extended inactivity.`);
} catch (err) {
await connection.rollback();
writeLog(`[Cleanup] Failed to hard delete user ${user.email}:`, err);
} finally {
connection.release();
}
}
} catch (error) {
writeLog('[Cleanup] Critical execution error:', error);
} finally {
if (db) {
await db.end();
writeLog('[Cleanup] Database connection pool closed.');
}
}
}
--- sql/model.ts ---
export interface ClientBloodTestReport {
report_id: number;
client_consultation_id: number;
report_date?: string | null;
created_at?: Date | string | null;
updated_at?: Date | string | null;
}
export interface ClientBloodTestResult {
result_id: number;
report_id: number;
test_code: string; // e.g., hemoglobin, total_wbc
value?: string | null;
}
export interface ClientConsultation {
client_consultation_id: number;
created_at?: Date | string | null;
updated_at?: Date | string | null;
consultation_date?: Date | string | null;
gender?: string | null; // Male, Female, Other
marital_status?: string | null; // Single, Married, Divorced, etc.
height_cms?: number | null;
weight_kg?: number | null;
age_years?: number | null;
shift_duty?: string | null; // Yes, No
joint_family?: string | null; // Yes, No
is_vegetarian?: string | null; // Yes, No
is_vegan?: string | null; // Yes, No
is_jain?: string | null; // Yes, No
has_lactose_intolerance?: string | null; // Yes, No
date_of_payment?: Date | string | null;
health_issues?: string | null;
food_liking?: string | null;
food_disliking?: string | null;
job_description?: string | null;
job_timings?: string | null;
sedentary_status?: string | null; // Yes, No, Partly
travelling_frequency?: string | null; // No, Sometimes, Extensively
is_latest?: boolean | number;
is_finalized?: boolean | number;
is_food_plan_complete?: boolean | number; // 0=Pending, 1=Completed
user_id: number;
}
export interface ClientFollowUp {
follow_up_id: number;
client_id: number;
follow_up_date: Date | string;
client_report?: string | null;
admin_instructions?: string | null;
created_at?: Date | string | null;
updated_at?: Date | string | null;
}
export interface ClientFoodPlan {
plan_id: number;
client_consultation_id: number;
general_recommendations?: string | null;
additional_personal_recommendations?: string | null;
created_at?: Date | string | null;
updated_at?: Date | string | null;
created_by_admin_id?: number | null;
created_by_nutritionist_id?: number | null;
}
export interface ClientFoodPlanHourlyDetail {
detail_id: number;
plan_id: number;
time_slot: string; // e.g., 06:00, 13:00
present_intake?: string | null;
proposed_structure?: string | null;
additional_points?: string | null;
}
export interface ClientMedicalHistory {
history_id: number;
client_consultation_id: number;
family_medical_history?: string | null;
created_at?: Date | string | null;
updated_at?: Date | string | null;
}
export interface ClientMedication {
medication_id: number;
history_id: number;
diagnosis?: string | null;
medicine_name?: string | null;
power?: string | null;
timing?: string | null;
since_when?: string | null;
}
export interface GeneralFoodRecommendation {
id: number;
recommendations_text?: string | null;
last_updated_by?: number | null;
updated_at?: Date | string | null;
}
export interface Role {
role_id: number;
role_name: string;
}
export interface UserV2 {
user_id: number;
first_name: string;
last_name: string;
email: string;
password_hash: string;
mobile_number?: string | null;
is_active: boolean | number;
is_email_verified: boolean | number;
email_verification_token?: string | null;
password_reset_token?: string | null;
password_reset_expires_at?: Date | string | null;
created_at?: Date | string | null;
updated_at?: Date | string | null;
address_1?: string | null;
address_2?: string | null;
address_3?: string | null;
city?: string | null;
pincode?: string | null;
reference_source?: string | null;
assigned_nutritionist_id?: number | null;
assigned_executive_id?: number | null;
email_otp?: string | null;
email_otp_expires_at?: Date | string | null;
}
export interface UserRole {
user_id: number;
role_id: number;
}
--- sql/2_schema_setup.sql ---
-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Feb 07, 2026 at 03:14 PM
-- Server version: 8.0.36
-- PHP Version: 8.3.25
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `sevenpoi_myconsultation_db`
--
-- --------------------------------------------------------
--
-- Table structure for table `client_blood_test_reports`
--
CREATE TABLE `client_blood_test_reports` (
`report_id` int NOT NULL,
`client_consultation_id` int NOT NULL,
`report_date` varchar(10) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `client_blood_test_reports`
--
INSERT INTO `client_blood_test_reports` (`report_id`, `client_consultation_id`, `report_date`, `created_at`, `updated_at`) VALUES
(1, 3, '14/01/2026', '2026-02-02 11:16:41', '2026-02-02 11:16:41'),
(2, 4, '2026-01-27', '2026-02-04 12:40:22', '2026-02-04 12:40:22');
-- --------------------------------------------------------
--
-- Table structure for table `client_blood_test_results`
--
CREATE TABLE `client_blood_test_results` (
`result_id` int NOT NULL,
`report_id` int NOT NULL,
`test_code` varchar(100) NOT NULL COMMENT 'e.g., hemoglobin, total_wbc. This should match the name attribute prefix in your form.',
`value` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `client_blood_test_results`
--
INSERT INTO `client_blood_test_results` (`result_id`, `report_id`, `test_code`, `value`) VALUES
(1, 1, 'hemoglobin', '11.9 gm/dL'),
(2, 1, 'total_wbc', '5910'),
(3, 1, 'total_rbc', '4.9'),
(4, 1, 'platelet', '331'),
(5, 1, 'pcv', '38.1'),
(6, 1, 'mcv', '77.8'),
(7, 1, 'mch', '24.3'),
(8, 1, 'mchc', '31.2'),
(9, 1, 'rdw', '15.2'),
(10, 1, 'eosinophils', '2.4'),
(11, 1, 'fasting_glucose', '80'),
(12, 1, 'fasting_insulin', '5.1'),
(13, 1, 'hba1c_hplc', '5.2'),
(14, 1, 'avg_plasma_glucose', '102.54'),
(15, 1, 'uric_acid', '4.6'),
(16, 1, 'bun', '10.9'),
(17, 1, 's_creatinine', '0.71'),
(18, 1, 'sodium', '137'),
(19, 1, 'chloride', '105'),
(20, 1, 'egfr', '121.7'),
(21, 1, 'bilirubin_total', '0.3'),
(22, 1, 'bilirubin_direct', '0.1'),
(23, 1, 'bilirubin_indirect', '0.2'),
(24, 1, 'sgpt_alt', '7'),
(25, 1, 'sgot', '15'),
(26, 1, 'protein', '7.1'),
(27, 1, 'albumin', '4.55'),
(28, 1, 'globulin', '2.55'),
(29, 1, 'ag_ratio', '1.78'),
(30, 1, 'homocystin', '10.22'),
(31, 1, 'cholesterol', '176'),
(32, 1, 'triglyceride', '77'),
(33, 1, 'hdl', '47'),
(34, 1, 'ldl', '113.6'),
(35, 1, 'vldl', '15.4'),
(36, 1, 'ldl_hdl_ratio', '2.4'),
(37, 1, 'chol_hdl_ratio', '3.74'),
(38, 1, 'crp', '1.08'),
(39, 1, 'vit_d3', '20.2'),
(40, 1, 'vit_b12', '355'),
(41, 1, 't3', '2.38'),
(42, 1, 't4', '0.95'),
(43, 1, 'tsh', '1.02'),
(44, 2, 'hemoglobin', '12.17'),
(45, 2, 'total_rbc', '4.53'),
(46, 2, 'platelet', '407 thou/mm3'),
(47, 2, 'pcv', '37.20'),
(48, 2, 'mcv', '82.20'),
(49, 2, 'mch', '26.90'),
(50, 2, 'mchc', '32.70'),
(51, 2, 'rdw', '14.70'),
(52, 2, 'eosinophils', '4.80'),
(53, 2, 'fasting_glucose', '105'),
(54, 2, 'hba1c_hplc', '5.8'),
(55, 2, 'uric_acid', '4.52'),
(56, 2, 'bun', '9.96'),
(57, 2, 's_creatinine', '0.71'),
(58, 2, 'bun_creatinine_ratio', '14'),
(59, 2, 'sodium', '137'),
(60, 2, 'chloride', '102'),
(61, 2, 'egfr', '120'),
(62, 2, 'bilirubin_total', '0.43'),
(63, 2, 'bilirubin_direct', '0.07'),
(64, 2, 'bilirubin_indirect', '0.36'),
(65, 2, 'sgpt_alt', '11.5'),
(66, 2, 'sgot', '21.9'),
(67, 2, 'protein', '7.71'),
(68, 2, 'albumin', '4.65'),
(69, 2, 'globulin', '3.06'),
(70, 2, 'ag_ratio', '1.52'),
(71, 2, 'cholesterol', '208.97'),
(72, 2, 'triglyceride', '104.80'),
(73, 2, 'hdl', '42.18'),
(74, 2, 'ldl', '145.83'),
(75, 2, 'vldl', '20.96'),
(76, 2, 'homocystin', '8.32'),
(77, 2, 'crp', '5.19'),
(78, 2, 'vit_d3', '33.06 nmol/L'),
(79, 2, 'vit_b12', '461.53'),
(80, 2, 'testosterone', '26.83'),
(81, 2, 't3', '0.86 ng/ml'),
(82, 2, 't4', '8.81 ug/dl'),
(83, 2, 'tsh', '1.57 ulu/ml');
-- --------------------------------------------------------
--
-- Table structure for table `client_consultations`
--
CREATE TABLE `client_consultations` (
`client_consultation_id` int NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`consultation_date` datetime DEFAULT CURRENT_TIMESTAMP,
`gender` varchar(10) DEFAULT NULL COMMENT 'Male, Female, Other',
`marital_status` varchar(20) DEFAULT NULL COMMENT 'Single, Married, Divorced, Widowed, Prefer not to say',
`height_cms` decimal(5,1) DEFAULT NULL,
`weight_kg` decimal(5,1) DEFAULT NULL,
`age_years` int DEFAULT NULL,
`shift_duty` varchar(3) DEFAULT NULL COMMENT 'Yes, No',
`joint_family` varchar(3) DEFAULT NULL COMMENT 'Yes, No',
`is_vegetarian` varchar(3) DEFAULT NULL COMMENT 'Yes, No',
`is_vegan` varchar(3) DEFAULT NULL COMMENT 'Yes, No',
`is_jain` varchar(3) DEFAULT NULL COMMENT 'Yes, No',
`has_lactose_intolerance` varchar(3) DEFAULT NULL COMMENT 'Yes, No',
`date_of_payment` date DEFAULT NULL,
`health_issues` text,
`food_liking` text,
`food_disliking` text,
`job_description` text,
`job_timings` varchar(100) DEFAULT NULL,
`sedentary_status` varchar(10) DEFAULT NULL COMMENT 'Yes, No, Partly',
`travelling_frequency` varchar(20) DEFAULT NULL COMMENT 'No, Sometimes, Extensively',
`is_latest` tinyint(1) DEFAULT '1',
`is_finalized` tinyint(1) DEFAULT '0',
`is_food_plan_complete` tinyint(1) DEFAULT '0' COMMENT '0=Pending, 1=Completed',
`user_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `client_consultations`
--
INSERT INTO `client_consultations` (`client_consultation_id`, `created_at`, `updated_at`, `consultation_date`, `gender`, `marital_status`, `height_cms`, `weight_kg`, `age_years`, `shift_duty`, `joint_family`, `is_vegetarian`, `is_vegan`, `is_jain`, `has_lactose_intolerance`, `date_of_payment`, `health_issues`, `food_liking`, `food_disliking`, `job_description`, `job_timings`, `sedentary_status`, `travelling_frequency`, `is_latest`, `is_finalized`, `is_food_plan_complete`, `user_id`) VALUES
(1, '2026-01-12 12:34:55', '2026-01-12 12:34:55', '2026-01-12 18:04:55', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 0, 2),
(2, '2026-01-18 07:31:20', '2026-01-18 07:36:36', '2026-01-18 13:01:20', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 0, 3),
(3, '2026-01-23 16:20:27', '2026-02-02 11:55:28', '2026-01-23 21:50:27', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 0, 4),
(4, '2026-01-30 06:55:15', '2026-02-05 06:08:57', '2026-01-30 12:25:15', 'Female', 'Single', 153.6, 48.0, 26, 'No', 'No', 'Yes', 'No', 'No', 'Yes', '2026-01-30', 'acidity, eyesight, inflammation on the face, acne-prone skin, anxiety', 'paratha bread aalo sabzi idli dosa pav bhaji pani puri soup pasta bajra roti with gud daal chawal', 'karela tinda roti ', 'Architect', NULL, NULL, 'Extensively', 1, 0, 0, 5),
(5, '2026-02-01 12:00:04', '2026-02-02 11:28:52', '2026-02-01 17:30:04', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 0, 6),
(6, '2026-02-01 12:59:42', '2026-02-01 12:59:42', '2026-02-01 18:29:42', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 0, 7),
(7, '2026-02-02 11:24:27', '2026-02-02 11:24:27', '2026-02-02 16:54:27', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 0, 8),
(8, '2026-02-02 11:32:58', '2026-02-02 11:32:58', '2026-02-02 17:02:58', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 0, 9),
(9, '2026-02-03 15:58:42', '2026-02-05 08:10:08', '2026-02-03 21:28:42', 'Female', 'Married', 5.0, 68.0, 36, 'No', 'Yes', 'No', NULL, 'Yes', NULL, '2026-02-03', 'sugar ,cholesterol high, weekness ', 'normal ', NULL, 'seating job - back office 9 hr insurer company ', '9.30 to 7', 'Yes', 'Extensively', 1, 0, 0, 10);
-- --------------------------------------------------------
--
-- Table structure for table `client_follow_ups`
--
CREATE TABLE `client_follow_ups` (
`follow_up_id` int NOT NULL,
`client_id` int NOT NULL,
`follow_up_date` date NOT NULL,
`client_report` text,
`admin_instructions` text,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- --------------------------------------------------------
--
-- Table structure for table `client_food_plans`
--
CREATE TABLE `client_food_plans` (
`plan_id` int NOT NULL,
`client_consultation_id` int NOT NULL,
`general_recommendations` text,
`additional_personal_recommendations` text,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_by_admin_id` int DEFAULT NULL,
`created_by_nutritionist_id` int UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `client_food_plans`
--
INSERT INTO `client_food_plans` (`plan_id`, `client_consultation_id`, `general_recommendations`, `additional_personal_recommendations`, `created_at`, `updated_at`, `created_by_admin_id`, `created_by_nutritionist_id`) VALUES
(1, 2, NULL, NULL, '2026-01-18 07:36:36', '2026-01-18 07:36:36', NULL, NULL),
(2, 5, NULL, NULL, '2026-02-02 11:28:52', '2026-02-02 11:28:52', NULL, NULL),
(4, 3, NULL, '- Take colostrum daily once
- Take Vit D 60 k once a week for 8 weeks and then once a month for 12 months
', '2026-02-03 21:43:03', '2026-02-03 21:43:03', 1, NULL),
(6, 4, NULL, '- Grape seed extracts {HImalayan Organics) twice a day for 3 months
', '2026-02-06 00:57:34', '2026-02-06 00:57:34', 1, NULL);
-- --------------------------------------------------------
--
-- Table structure for table `client_food_plan_hourly_details`
--
CREATE TABLE `client_food_plan_hourly_details` (
`detail_id` int NOT NULL,
`plan_id` int NOT NULL,
`time_slot` varchar(10) NOT NULL COMMENT 'e.g., 06:00, 13:00',
`present_intake` text,
`proposed_structure` text,
`additional_points` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `client_food_plan_hourly_details`
--
INSERT INTO `client_food_plan_hourly_details` (`detail_id`, `plan_id`, `time_slot`, `present_intake`, `proposed_structure`, `additional_points`) VALUES
(1, 1, '06:00', 'ukala', NULL, NULL),
(19, 4, '08:00', 'Water', NULL, NULL),
(20, 4, '09:00', 'Water + Dry fruits', 'Avoid any type of food at this time', NULL),
(21, 4, '10:00', 'Water + Fruits\nBanana/ kiwi/ Orange/ Blueberry/ Pomegranate/ Pineapple', 'Avoid any type of fruit at this time', NULL),
(22, 4, '11:00', 'Water + Breakfast\neggs, tomato, lettuce, cheese, Meal replacement drink (Huel)', 'Soup or Vegetable Juice, Soaked nuts, Seasonal Fruits (Please eat fruits 20 minutes before other food)', NULL),
(23, 4, '12:00', 'Water', NULL, NULL),
(24, 4, '13:00', 'Water', NULL, NULL),
(25, 4, '14:00', 'Water + Lunch\nveg: Rice, dal, potato paratha, rajma, chole, protein wraps with brocoli,corn,onion,tomato,lettuce,potato pattice\nNon-veg: chicken, egg, rice, chapati, protein wraps with brocoli,corn,onion,tomato,lettuce,chicken', 'Your food intake for lunch is correct but also take Milkshake with Protein, curd, limboo pickle', NULL),
(26, 4, '15:00', 'Water', NULL, NULL),
(27, 4, '16:00', 'Water', NULL, NULL),
(28, 4, '17:00', 'Water', NULL, NULL),
(29, 4, '18:00', 'Water + Chiracafe', 'Chiracafe is ok ,but without milk', NULL),
(30, 4, '19:00', 'Water', NULL, NULL),
(31, 4, '20:00', 'Water', NULL, NULL),
(32, 4, '21:00', 'Water + Lunch\nveg: Rice, dal, potato paratha, rajma, chole, protein wraps with brocoli,corn,onion,tomato,lettuce,potato pattice\nNon-veg: chicken, egg, rice, chapati, protein wraps with brocoli,corn,onion,tomato,lettuce,chicken, egg fried rice and chicken', 'Your food intake for dinner is correct but In addition to your food intake take curd, galic pickle, flax seed chatni, ', NULL),
(33, 4, '22:00', 'Water', NULL, NULL),
(34, 4, '23:00', 'Water', NULL, NULL),
(35, 4, '00:00', 'Water', NULL, NULL),
(41, 6, '09:00', 'chia seed-soaked nuts peanut butter bread banana', 'Vegetable Juice, Soaked nuts / Paneer recipe ', NULL),
(42, 6, '11:00', 'papaya protein powder', 'Protein Ladoo / Bar / DRufruit Chikki', NULL),
(43, 6, '13:00', '2 wheat roti sabji dahi ', 'Jawar / Bajra Bhakri , Palebhaji, curd, 3 colored salad, falx seed chatni, dryfruit milkshake with Whey protein + fruit smoothie', NULL),
(44, 6, '16:00', 'bread butter', 'Detox Drink ( I will send the recipe) ', NULL),
(45, 6, '21:00', 'dal chawal paneer ghee ', 'Soup + green smoothie, Usal, Rice, Paneer, Curd, 3 colored salad, Garlic pickle', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `client_medical_history`
--
CREATE TABLE `client_medical_history` (
`history_id` int NOT NULL,
`client_consultation_id` int NOT NULL,
`family_medical_history` text,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `client_medical_history`
--
INSERT INTO `client_medical_history` (`history_id`, `client_consultation_id`, `family_medical_history`, `created_at`, `updated_at`) VALUES
(1, 3, 'No medical history.\n', '2026-02-02 11:18:13', '2026-02-02 11:18:13'),
(3, 4, 'Sugar and arthritis ', '2026-02-04 12:08:11', '2026-02-04 12:08:11');
-- --------------------------------------------------------
--
-- Table structure for table `client_medications`
--
CREATE TABLE `client_medications` (
`medication_id` int NOT NULL,
`history_id` int NOT NULL,
`diagnosis` varchar(255) DEFAULT NULL,
`medicine_name` varchar(255) DEFAULT NULL,
`power` varchar(100) DEFAULT NULL,
`timing` varchar(100) DEFAULT NULL,
`since_when` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- --------------------------------------------------------
--
-- Table structure for table `general_food_recommendations`
--
CREATE TABLE `general_food_recommendations` (
`id` int NOT NULL,
`recommendations_text` text,
`last_updated_by` int DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `general_food_recommendations`
--
INSERT INTO `general_food_recommendations` (`id`, `recommendations_text`, `last_updated_by`, `updated_at`) VALUES
(1, 'General guidelines:-
(1) Vegetable Juice means - Doodhi (1) : Kakdi (1) : Gajar (1/2) : Beet (1/4) :Tomato (1/4) in this proportion. Use Juiceer as far as possible. If you add little water , Mixer will also do. But drink CLEAR Juice, without fiber.
(2) For Curcumin, Omega 3 capsule please contact Prasanna Rasayan, Thane – 9869418118.
(3) For 7.4 Farsan and Sweets Please either make it at home or get it from Rajashree Shivpuje, Kurla Mumbai - 9322594323
(4) If possible, change from A1 milk to A2 milk.
(5) For CMD (concentrated minerals drops) contact Dr Ketan Patkar – 9222221620
(6) If you feel hungry in between twi food servings you may have any of these drinks --Ukala / 7.4 Coffee / Butter milk / Masala milk / Soup / Limboo Sarbat / Solkadhi / Kheer – Doodhi / Carrot / Lalbhopla, khova, nuts, milk masala.Hibiscus tea, Jasmine tea, [(Pudina + Coriender + Panipuri masala + Ginger Shot (simple words- Panipuri water) + Chia seeds water.] (Use Stevia as a sweetener)
(7) Paan : Paan + Pumpkin seeds + Flax seed + Ilachi + Ghreen Patti + Jysthimadh + Katha + chuna.
(8) Link for Himalayan Organics - Multivitamin with probiotics - https://www.amazon.in/Himalayan-Organics-Multivitamin-Probiotics-Ingredients/dp/B0B5R7FX3H/ref=sr_1_5adgrpid=1327112144297213&hvadid=82944767671541&hvbmt=be&hvdev=c&hvlocphy=90&hvnetw=o&hvqmt=e&hvtargid=kwd-82945389859439%3Aloc-90&hydadcr=2534_1936664&keywords=himalayan+organics+multivitamin+with+probiotics&qid=1683773166&sr=8-5
(9) LInk for Himalayan Organics - Grape seed Extracts - https://www.amazon.in/Himalayan-Organics-Antioxidant-Supplement-Cholesterol/dp/B09X5D5BJG?th=1
(10) For Stevia (natural alternative to sugar) contact Arvind Sane - 9850746172
(11) For FOS (natural alternative to sugar)- Dr Mrunal Saraf - 9324140899
Personal Guidlines:-
(1) Please practice every day for 15 minutes – Kapalbhati, Agnisar Anulom Vilom
(2) 15 minutes sleeping resting posture as given in my book 7.4 or You Tube
(3) 30 minutes casual walk and drink 150 ml water per hour.
(4) Himalayan multivitamin tablets with probiotic twice a day for 3 months
(5) 10 CMD (concentrated minerals drops) in the morning with 150 ml water (for 6 months)
(6) Implement the above food plan step by step. Follow 36 hours fasting (only water) every 15 days. You may proceed step by step, it is ok
(7) Learn and Practice Dhanwantary Yog for 15 minutes daily.
(8) I have observed that many a times clients feel that there is no variety and within a short period he gets bored with the food plan. To address this issue I give below following links to help you out of this problems. The ingrediants used are not 7.4 compatible. But you can select healthy options and make these racipes and enjoy.
', 1, '2026-02-03 08:06:39');
-- --------------------------------------------------------
--
-- Table structure for table `roles`
--
CREATE TABLE `roles` (
`role_id` int NOT NULL,
`role_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `roles`
--
INSERT INTO `roles` (`role_id`, `role_name`) VALUES
(1, 'admin'),
(2, 'client'),
(5, 'executive'),
(4, 'nutritionist'),
(3, 'staff');
-- --------------------------------------------------------
--
-- Table structure for table `users_v2`
--
CREATE TABLE `users_v2` (
`user_id` int NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password_hash` varchar(255) NOT NULL,
`mobile_number` varchar(20) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
`is_email_verified` tinyint(1) NOT NULL DEFAULT '0',
`email_verification_token` varchar(255) DEFAULT NULL,
`password_reset_token` varchar(255) DEFAULT NULL,
`password_reset_expires_at` datetime DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address_1` varchar(255) DEFAULT NULL,
`address_2` varchar(255) DEFAULT NULL,
`address_3` varchar(255) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`pincode` varchar(10) DEFAULT NULL,
`reference_source` varchar(255) DEFAULT NULL,
`assigned_nutritionist_id` int DEFAULT NULL,
`assigned_executive_id` int DEFAULT NULL,
`email_otp` varchar(10) DEFAULT NULL,
`email_otp_expires_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `users_v2`
--
INSERT INTO `users_v2` (`user_id`, `first_name`, `last_name`, `email`, `password_hash`, `mobile_number`, `is_active`, `is_email_verified`, `email_verification_token`, `password_reset_token`, `password_reset_expires_at`, `created_at`, `updated_at`, `address_1`, `address_2`, `address_3`, `city`, `pincode`, `reference_source`, `assigned_nutritionist_id`, `assigned_executive_id`, `email_otp`, `email_otp_expires_at`) VALUES
(1, 'Madhav', 'Joshi', 'madhavjoshi02@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', NULL, 1, 1, NULL, NULL, NULL, '2025-12-21 10:44:37', '2025-12-21 10:44:37', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(2, 'Manasi', 'A', 'manasi@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567890', 1, 1, NULL, NULL, NULL, '2026-01-12 12:34:55', '2026-01-12 19:14:21', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(3, 'Asmita', 'A', 'asmita@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567891', 1, 1, NULL, NULL, NULL, '2026-01-18 07:31:20', '2026-01-18 07:33:02', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(4, 'Shreya R', 'Bane', 'shreya@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567892', 1, 1, NULL, NULL, NULL, '2026-01-23 16:20:27', '2026-01-24 02:03:24', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(5, 'Sanskruti', 'T', 'sanskruti@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567893', 1, 1, NULL, NULL, NULL, '2026-01-30 06:55:15', '2026-02-05 06:08:57', 'Vedant Sapphire Sneh Nagar ', '', '', 'Nagpur', '440015', 'Mona Jain', NULL, NULL, NULL, NULL),
(6, 'Vrushali', 'B', 'vrushali@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567894', 1, 1, NULL, NULL, NULL, '2026-02-01 12:00:04', '2026-02-01 12:08:45', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(7, 'Dr Saylee', 'U', 'drsaylee@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567895', 1, 1, NULL, NULL, NULL, '2026-02-01 12:59:42', '2026-02-01 19:17:11', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(8, 'Vidya', 'K', 'vidya@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '+1234567896', 1, 1, NULL, NULL, NULL, '2026-02-02 11:24:27', '2026-02-02 11:34:46', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(9, 'Asawari', 'V', 'asawari@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567897', 1, 1, NULL, NULL, NULL, '2026-02-02 11:32:58', '2026-02-02 11:34:49', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(10, 'Ashwini ', 'C', 'ashwini@test.com', '$2b$10$t6DwmfFSAmjrxT7MDxCQQ.GSRmY1/Qq6f77uZ5WFcps4NGYf.fhHu', '1234567898', 1, 1, NULL, NULL, NULL, '2026-02-03 15:58:42', '2026-02-05 08:10:08', '24 /C/003 Sai sadan chandivali mahada colony andheri east 4000072', '', '', '', '400072', 'prakalp Talwalkar', NULL, NULL, NULL, NULL);
-- --------------------------------------------------------
--
-- Table structure for table `user_roles`
--
CREATE TABLE `user_roles` (
`user_id` int NOT NULL,
`role_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `user_roles`
--
INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
(1, 1),
(2, 2),
(3, 2),
(4, 2),
(5, 2),
(6, 2),
(7, 2),
(8, 2),
(9, 2),
(10, 2);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `client_blood_test_reports`
--
ALTER TABLE `client_blood_test_reports`
ADD PRIMARY KEY (`report_id`),
ADD KEY `client_consultation_id` (`client_consultation_id`);
--
-- Indexes for table `client_blood_test_results`
--
ALTER TABLE `client_blood_test_results`
ADD PRIMARY KEY (`result_id`),
ADD KEY `report_id` (`report_id`),
ADD KEY `test_code` (`test_code`);
--
-- Indexes for table `client_consultations`
--
ALTER TABLE `client_consultations`
ADD PRIMARY KEY (`client_consultation_id`),
ADD KEY `fk_consultation_user` (`user_id`);
--
-- Indexes for table `client_follow_ups`
--
ALTER TABLE `client_follow_ups`
ADD PRIMARY KEY (`follow_up_id`),
ADD UNIQUE KEY `client_id` (`client_id`,`follow_up_date`);
--
-- Indexes for table `client_food_plans`
--
ALTER TABLE `client_food_plans`
ADD PRIMARY KEY (`plan_id`),
ADD KEY `fk_created_by_admin` (`created_by_admin_id`),
ADD KEY `client_food_plans_ibfk_1` (`client_consultation_id`);
--
-- Indexes for table `client_food_plan_hourly_details`
--
ALTER TABLE `client_food_plan_hourly_details`
ADD PRIMARY KEY (`detail_id`),
ADD KEY `plan_id` (`plan_id`,`time_slot`);
--
-- Indexes for table `client_medical_history`
--
ALTER TABLE `client_medical_history`
ADD PRIMARY KEY (`history_id`),
ADD KEY `client_consultation_id` (`client_consultation_id`);
--
-- Indexes for table `client_medications`
--
ALTER TABLE `client_medications`
ADD PRIMARY KEY (`medication_id`),
ADD KEY `history_id` (`history_id`);
--
-- Indexes for table `general_food_recommendations`
--
ALTER TABLE `general_food_recommendations`
ADD PRIMARY KEY (`id`),
ADD KEY `last_updated_by` (`last_updated_by`);
--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
ADD PRIMARY KEY (`role_id`),
ADD UNIQUE KEY `role_name` (`role_name`);
--
-- Indexes for table `users_v2`
--
ALTER TABLE `users_v2`
ADD PRIMARY KEY (`user_id`),
ADD UNIQUE KEY `email` (`email`),
ADD KEY `fk_users_v2_nutritionist` (`assigned_nutritionist_id`),
ADD KEY `fk_users_v2_executive` (`assigned_executive_id`);
--
-- Indexes for table `user_roles`
--
ALTER TABLE `user_roles`
ADD PRIMARY KEY (`user_id`,`role_id`),
ADD KEY `fk_user_roles_role_id` (`role_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `client_blood_test_reports`
--
ALTER TABLE `client_blood_test_reports`
MODIFY `report_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `client_blood_test_results`
--
ALTER TABLE `client_blood_test_results`
MODIFY `result_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=84;
--
-- AUTO_INCREMENT for table `client_consultations`
--
ALTER TABLE `client_consultations`
MODIFY `client_consultation_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
--
-- AUTO_INCREMENT for table `client_follow_ups`
--
ALTER TABLE `client_follow_ups`
MODIFY `follow_up_id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `client_food_plans`
--
ALTER TABLE `client_food_plans`
MODIFY `plan_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `client_food_plan_hourly_details`
--
ALTER TABLE `client_food_plan_hourly_details`
MODIFY `detail_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46;
--
-- AUTO_INCREMENT for table `client_medical_history`
--
ALTER TABLE `client_medical_history`
MODIFY `history_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `client_medications`
--
ALTER TABLE `client_medications`
MODIFY `medication_id` int NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `general_food_recommendations`
--
ALTER TABLE `general_food_recommendations`
MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
MODIFY `role_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `users_v2`
--
ALTER TABLE `users_v2`
MODIFY `user_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `client_blood_test_reports`
--
ALTER TABLE `client_blood_test_reports`
ADD CONSTRAINT `client_blood_test_reports_ibfk_1` FOREIGN KEY (`client_consultation_id`) REFERENCES `client_consultations` (`client_consultation_id`) ON DELETE CASCADE;
--
-- Constraints for table `client_blood_test_results`
--
ALTER TABLE `client_blood_test_results`
ADD CONSTRAINT `client_blood_test_results_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `client_blood_test_reports` (`report_id`) ON DELETE CASCADE;
--
-- Constraints for table `client_consultations`
--
ALTER TABLE `client_consultations`
ADD CONSTRAINT `fk_consultation_user` FOREIGN KEY (`user_id`) REFERENCES `users_v2` (`user_id`) ON DELETE CASCADE;
--
-- Constraints for table `client_food_plans`
--
ALTER TABLE `client_food_plans`
ADD CONSTRAINT `client_food_plans_ibfk_1` FOREIGN KEY (`client_consultation_id`) REFERENCES `client_consultations` (`client_consultation_id`) ON DELETE CASCADE;
--
-- Constraints for table `client_food_plan_hourly_details`
--
ALTER TABLE `client_food_plan_hourly_details`
ADD CONSTRAINT `client_food_plan_hourly_details_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `client_food_plans` (`plan_id`) ON DELETE CASCADE;
--
-- Constraints for table `client_medical_history`
--
ALTER TABLE `client_medical_history`
ADD CONSTRAINT `client_medical_history_ibfk_1` FOREIGN KEY (`client_consultation_id`) REFERENCES `client_consultations` (`client_consultation_id`) ON DELETE CASCADE;
--
-- Constraints for table `client_medications`
--
ALTER TABLE `client_medications`
ADD CONSTRAINT `client_medications_ibfk_1` FOREIGN KEY (`history_id`) REFERENCES `client_medical_history` (`history_id`) ON DELETE CASCADE;
--
-- Constraints for table `users_v2`
--
ALTER TABLE `users_v2`
ADD CONSTRAINT `fk_users_v2_executive` FOREIGN KEY (`assigned_executive_id`) REFERENCES `users_v2` (`user_id`) ON DELETE SET NULL,
ADD CONSTRAINT `fk_users_v2_nutritionist` FOREIGN KEY (`assigned_nutritionist_id`) REFERENCES `users_v2` (`user_id`) ON DELETE SET NULL;
--
-- Constraints for table `user_roles`
--
ALTER TABLE `user_roles`
ADD CONSTRAINT `fk_user_roles_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_user_roles_user_id` FOREIGN KEY (`user_id`) REFERENCES `users_v2` (`user_id`) ON DELETE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
--- ACCESS_CONTROL.md ---
# Access Control Register (DPDP Compliance)
This document records all individuals with administrative access to critical system components, ensuring compliance with data protection principles of least privilege and accountability.
## 1. Database Access (MySQL)
| Name | Email | Role/Permissions | Last Reviewed | Notes |
| :--- | :--- | :--- | :--- | :--- |
| [Your Name] | [Your Email] | Full Read/Write (Development) | [Current Date] | Primary Developer |
| [Guide's Name] | [Guide's Email] | Read-Only (Audit/Review) | [Current Date] | Project Guide |
| [Add Other Admins] | | | | |
## 2. Hosting / cPanel Access
| Name | Email | Role/Permissions | Last Reviewed | Notes |
| :--- | :--- | :--- | :--- | :--- |
| [Your Name] | [Your Email] | Full Access (Development/Deployment) | [Current Date] | Primary Developer |
| [Add Other Admins] | | | | |
## 3. Application Code Repository (GitHub/GitLab)
| Name | Email | Role/Permissions | Last Reviewed | Notes |
| :--- | :--- | :--- | :--- | :--- |
| [Your Name] | [Your Email] | Read/Write | [Current Date] | Primary Developer |
| [Guide's Name] | [Guide's Email] | Read/Write | [Current Date] | Project Guide |
| [Add Other Admins] | | | | |
--- BACKUP_LOG.md ---
# Backup System Verification Log (DPDP Compliance)
| Verification Item | Status | Details | Date Verified |
| :--- | :--- | :--- | :--- |
| Automated Backups Active? | Yes | Active for accounts under 10GB usage. | 2026-04-04 |
| Backup Frequency | Weekly | Single weekly copy configured. | 2026-04-04 |
| Storage Location | Off-site | Stored on a remote server. | 2026-04-04 |
| Retention Period | 1 Week | Single copy retention (shared server). | 2026-04-04 |
| Restore Process Tested? | Pending | | |
## Provider Confirmation Notes
Received from Hostripples Support (Ticket #445220):
1. Yes, the backup are automated and active for your account as its usage is below 10GB
2. For the server on which your account is hosted has weekly backup configured with single weekly copy.
3. Backup of your account are store on remote server.
4. For the server on which your account is hosted have single retention only as it is shared server.
5. Kindly note that restoration process is completely dependent upon the size of the account and latency between source and destination server.
Support URL: https://hostripples.in/secure/viewticket.php?tid=445220&c=S8dSIGjM
--- BACKUP_SECURITY_LOG.md ---
# Backup Security Verification Log (DPDP Compliance)
| Verification Item | Status | Details | Date Verified |
| :--- | :--- | :--- | :--- |
| Backup Files Encrypted? | No / Not Supported | AES-256 encryption not applied to stored data by default. | 2026-04-05 |
| Backups Publicly Accessible? | No | Access strictly controlled via host authentication. | 2026-04-05 |
| Backups on Same Server as Live? | No | Stored on a remote server. | 2026-04-05 |
| Backup Integrity Check | Verified | Weekly automated backups active for accounts < 10GB. | 2026-04-05 |
## Provider Confirmation Notes
Received from Hostripples Support:
1. Backups are automated and active (usage below 10GB).
2. Weekly backup configured with single weekly copy.
3. Stored on a remote server.
4. No disk-level or database-level encryption (AES-256) on shared environment.
Support Ticket ID: #445220
--- BREACH_RESPONSE.md ---
# Data Breach Response Plan
## 1. Definition of a Personal Data Breach
A breach of personal data includes any unauthorized processing, disclosure, alteration, or destruction of personal or health data that compromises the confidentiality, integrity, or availability of the information.
## 2. Immediate Response Steps
1. **Identification:** Isolate the affected system and stop the ongoing breach.
2. **Assessment:** Determine the nature and scale of the data affected (Identity vs. Health Data).
3. **Documentation:** Record the time of discovery and the specific data fields compromised.
## 3. Mandatory Notifications (DPDP Act Section 8(6))
In the event of a personal data breach, the Consultation Service will:
1. **Notify the Board:** Report the breach to the Data Protection Board of India in the prescribed form.
2. **Notify Affected Users:** Inform impacted Data Principals (Clients) about the breach and steps taken to mitigate harm.
### 3.1 Notification Content Checklist
When notifying the Data Protection Board and affected Data Principals, the communication must include:
* **Nature of the breach:** What happened, how it occurred, and what data was affected.
* **Categories of personal data involved:** E.g., Identity Data, Health Data, Financial Data.
* **Number of Data Principals affected:** Estimated count of individuals whose data was compromised.
* **Consequences of the breach:** Potential harm to Data Principals.
* **Measures taken or proposed to be taken:** Steps to mitigate the breach and prevent recurrence.
* **Contact information:** Details of the Grievance Officer or other point of contact for inquiries.
## 4. Remediation
* Review server logs (`writeLog` output) to identify the entry point.
* Reset passwords or revoke tokens if session data is compromised.
* Patch the vulnerability identified in the assessment.
## 5. Timeline
All notifications to the Board and affected users must be made without undue delay, and in any case within **72 hours** of the discovery of the breach.
## Contact Point
**Breach Response Coordinator:** Madhav Joshi (madhavjoshi02@gmail.com)
--- COMPLIANCE_AUDIT.md ---
# DPDP Act 2023 - Compliance Audit Checklist
| Compliance Group | Requirement | Status | Verification |
| :--- | :--- | :--- | :--- |
| **1. Consent** | Affirmative, non-pre-checked consent. | Done | Registration modal & DB storage verified. |
| **2. Privacy Notice** | Clear explanation of data usage. | Done | `privacy.html` linked in footer/registration. |
| **3. Erasure** | Right to be forgotten / Account deletion. | Done | Dashboard button & backend cleanup logic. |
| **4. Age Verification** | Block minors / Child safety. | Done | DOB field & registration block for < 18. |
| **5. Grievance** | Point of contact for complaints. | Done | Human contact details in policy and dashboard. |
| **6. Retention** | Data lifecycle and inactivity monitoring. | Done | `last_login` tracking & Admin UI highlighting. |
| **7. Disclosure** | Transparency on 3rd party processors. | Done | Google/Hosting processors listed in policy. |
| **8. Data Mapping** | Internal inventory of all data fields. | Done | `DATA_MAPPING.md` created and verified. |
| **9. Breach Response** | Procedure for reporting leaks. | Done | `BREACH_RESPONSE.md` created and verified. |
| **10. Final Audit** | Cross-verification of all steps. | Done | This checklist completed. |
| **11. Privacy Fixes** | Hindi translation, Minor policy, Escalation. | Done | Verified in `privacy.html` UI. |
| **12. Backup System** | Weekly remote backup verification. | Done | Documented in `BACKUP_LOG.md`. |
| **13. Log Retention** | 365-day automated log cleanup. | Done | `cleanup_old_logs.mjs` active in scheduler. |
| **14. Backup Sync** | Soft-delete logic to prevent data conflicts. | Done | `deleted` flag and blocks verified in DB/Auth. |
| **15. Inactive Cleanup** | 24-month auto-deletion + 48h warning. | Done | `inactive_user_cleanup.mjs` active in scheduler. (Now hard-delete) |
| **16. Access Control** | Admin/Staff access register. | Done | `ACCESS_CONTROL.md` created. |
| **17. Encryption** | Verification of data-at-rest encryption. | Done | Logged as 'Not Supported' per host reply. |
| **18. Backup Security** | Off-site storage and backup file security. | Done | Documented in `BACKUP_SECURITY_LOG.md`. |
| **19. Mapping Upgrade** | Legal Basis included in data inventory. | Done | `DATA_MAPPING.md` upgraded. |
| **20. Response Upgrade** | 72-hour timeline and notification checklist. | Done | `BREACH_RESPONSE.md` finalized. |
| **21. Security Hardening** | Content Security Policy (CSP) & Extended Rate Limiting. | Done | Helmet CSP configured to allow local and trusted scripts; rate limiting extended to `/api/forgot-password` and `/api/reset-password` in `server.mjs`. |
## Summary
The Consultation App has achieved 100% completion of all identified technical requirements, transitioning from a secure application to a legally and operationally compliant system under the Digital Personal Data Protection Act (DPDP Act) 2023.
--- DATA_MAPPING.md ---
# Data Processing Inventory (Data Mapping)
| Field Name | Data Category | Purpose of Processing | Required? | Retention Period | Legal Basis |
| :--- | :--- | :--- | :--- | :--- | :--- |
| first_name, last_name | Identity | Identification and personalized communication. | Yes | Account Lifetime | Affirmative Consent |
| email | Identity | Primary login identifier and consultation notifications. | Yes | Account Lifetime | Affirmative Consent |
| mobile_number | Identity | Contact method for nutritionist/executive follow-up. | Yes | Account Lifetime | Affirmative Consent |
| date_of_birth | Identity | Age verification to comply with child protection laws. | Yes | Account Lifetime | Affirmative Consent |
| medical_history | Health Data | Customizing the 7.4 food plan based on health conditions. | Yes | Active Lifecycle + 2 Years | Affirmative Consent |
| blood_test_results | Health Data | Tracking progress and vital signs during consultation. | Yes | Active Lifecycle + 2 Years | Affirmative Consent |
| food_preferences | Lifestyle | Customizing food plan structure and structure. | Yes | Active Lifecycle + 2 Years | Affirmative Consent |
| consent_given | Legal | Proof of affirmative action per DPDP Act 2023. | Yes | Permanent (Legal Audit) | Legal Obligation |
| consent_timestamp | Legal | Verification of the exact moment consent was obtained. | Yes | Permanent (Legal Audit) | Legal Obligation |
| last_login | Audit | Monitoring inactivity to trigger auto-deletion policy. | Yes | 2 Years | Legitimate Use |
## Legal Basis
All personal and health data processing is based on the **Affirmative Consent** provided by the Data Principal (User) during the registration process, as defined in Section 6 of the DPDP Act 2023.
--- ENCRYPTION_LOG.md ---
# Data Encryption Verification Log (DPDP Compliance)
| Verification Item | Status | Details | Date Verified |
| :--- | :--- | :--- | :--- |
| Database Encrypted at Rest? | No / Not Supported | Access controlled via authentication and permissions. | 2026-04-05 |
| File Storage Encrypted at Rest? | No / Not Supported | Standard shared hosting; relies on perimeter security. | 2026-04-05 |
| Encryption Standard | N/A | AES-256 not applied to stored data by default. | 2026-04-05 |
## Provider Confirmation Notes
Received from Hostripples Support:
Database Encryption (MySQL): The MySQL databases on our shared hosting servers are not encrypted at rest by default at the individual database level. However, access to databases is strictly controlled via authentication, permissions, and secure network configurations.
File Storage Encryption: The server disks used in our standard shared hosting environment are not configured with full disk encryption (FDE). Data is stored on secured storage systems with strict access controls and monitoring in place.
Encryption Standards: Since encryption at rest is not enabled by default on this hosting plan, AES-256 (or similar disk-level encryption standards) is not currently applied to stored data.
Recommendation: Upgrading to a dedicated server or VPS environment would allow for full disk encryption (LUKS or similar) and database-level encryption.
--- mapping.md ---
The following mapping table outlines the application's primary top-level pages, identifying their core UI fields and tracing them through frontend API calls and backend route handlers to their underlying database models.
| Page Name | File Name | UI Fields (Inputs/Labels/Displays) | Underlying Data Model Objects |
| :--- | :--- | :--- | :--- |
| **Unified Login** | `login.html` | Email, Password, OTP Input | `users_v2` (for authentication), `user_roles` (for role verification) |
| **Client Registration** | `register.html` | First Name, Last Name, Mobile Number, Email, Password, Confirm Password | `users_v2` (user account), `user_roles` (role assignment), `client_consultations` (initial consultation record) |
| **Admin: Manage Staff** | `admin.html` | **Add Staff:** First Name, Last Name, Email, Initial Password, Roles (Admin, Nutritionist, Executive). **Staff List:** ID, Name, Email, Roles, Active Status. | `users_v2` (staff accounts), `user_roles` (staff permissions), `roles` (role definitions) |
| **Admin: Manage Clients** | `manage-clients.html` | **Search:** Client ID, First/Last Name, Email. **Client Table:** ID, Name, Email, Mobile, Registration Date, Verification Status, Assigned Staff. | `users_v2` (client details), `client_consultations` (status/latest info), `user_roles` |
| **Client: Personal Details** | `client-personal-details-form.html` | Address (1, 2, 3), City, Pincode, Height (cms), Weight (kg), Age, Gender, Marital Status, Shift Duty, Joint Family, Vegetarian, Vegan, Jain, Lactose Intolerance, Health Issues, Job Description/Timings, Sedentary Status, Travelling Frequency | `users_v2` (address/contact), `client_consultations` (physical, lifestyle, and dietary details) |
| **Client: Blood Tests** | `client-blood-tests-form.html` | Report Date, Blood Test Parameter Inputs (e.g., Hemoglobin, Fasting Glucose, Uric Acid, SGPT, Cholesterol, TSH) | `client_blood_test_reports` (report metadata/date), `client_blood_test_results` (individual test values mapped via `test_code`) |
| **Client: Medical History** | `client-medical-history-form.html` | Family Medical History, Medications Grid (Diagnosis, Medicine Name, Power, Timing, Since When) | `client_medical_history` (family history), `client_medications` (specific medicine entries linked via `history_id`) |
| **Client: Food Plan** | `client-food-plan.html` | **Hourly Grid:** Time, Present Intake, Proposed Structure, Additional Points. **Text Areas:** Personal Recommendations, Admin Recommendations. | `client_food_plans` (general and personal recommendations), `client_food_plan_hourly_details` (hourly schedule data) |
| **General Recommendations** | `general-recommendations.html` | Recommendations Text Editor/Input | `general_food_recommendations` |
| **System Statistics** | `statistics.html` | Displays: Total Nutritionists, Executives, Active/Pending Clients, History Submissions, Plans Suggested/Sent | Counts aggregated via `users_v2`, `user_roles`, `client_consultations`, and `client_food_plans` |
| **Client: Dashboard** | `client-dashboard.html` | Displays: Client Name, Email, Client ID. Actions: Logout (button), Delete My Account (button). | `users_v2` (profile data and user activation status) |
| **Client: Select Team** | `client-select-staff.html` | Inputs: Client ID, Nutritionist Selection dropdown, Executive Selection dropdown. | `users_v2` (updates `assigned_nutritionist_id` and `assigned_executive_id` on the client's record; retrieves active staff list) |
| **Client/Staff: Consultation History** | `compare.html` | Tabs: Blood Reports, Food Plan. Displays: Pivot table comparing all historic consultation details for the client. | Aggregates across `client_consultations`, `client_blood_test_reports`, `client_blood_test_results`, `client_medical_history`, `client_medications`, `client_food_plans`, `client_food_plan_hourly_details`. |
| **Nutritionist: My Clients** | `nutritionist-dashboard.html` | **Search:** Client ID, First/Last Name, Email. **Table:** ID, Name, Email, Mobile. **Biodata Modal:** Read-only client details. **Med History Modal:** Read-only family medical history, medications grid. **Blood Tests Modal:** Read-only blood test results. **Food Plan Modal:** Hourly Food Plan inputs (Present Intake, Proposed Structure, Additional Points), Additional Personal Recommendations (editable), General Recommendations (read-only). | `users_v2` (assigned clients list), `client_consultations`, `client_medical_history`, `client_medications`, `client_blood_test_reports`, `client_blood_test_results`, `client_food_plans` (inserts/deletes), `client_food_plan_hourly_details` (inserts), `general_food_recommendations` (read-only). |
| **Nutritionist: Statistics** | `nutritionist-statistics.html` | Displays: Total Assigned Clients, Final History Submitted, Food Plan Suggested, Food Plan Sent. | `users_v2` (client assignment count), `client_consultations` (status filters: is_finalized, is_food_plan_complete), `client_food_plans` (suggested plan counts). |
| **Executive: Enrolled Clients** | `executive-dashboard.html` | **Search:** Client ID, First/Last Name, Email. **Table:** ID, Name, Email, Mobile, Registration Date. **Modals (Read-only):** Biodata, Med History, Blood Tests, Food Plan (Hourly display, Personal/General recommendations). | `users_v2` (enrolled clients list), `client_consultations`, `client_medical_history`, `client_medications`, `client_blood_test_reports`, `client_blood_test_results`, `client_food_plans`, `client_food_plan_hourly_details`, `general_food_recommendations`. |
| **Executive: Statistics** | `executive-statistics.html` | Displays: Total Enrolled Clients, Clients Pending Activation, Final History Submitted, Food Plan Suggested, Food Plan Sent. | `users_v2` (client enrollment count, activity status), `client_consultations` (status filters), `client_food_plans`. |
| **Forgot Password** | `forgot-password.html` | Email Input. | `users_v2` (updates `password_reset_token` and `password_reset_expires_at`). |
| **Reset Password** | `reset-password.html` | New Password, Confirm New Password Inputs. | `users_v2` (updates `password_hash`, clears reset token fields). |
| **Verify Email OTP** | `verify-otp.html` | Email, OTP Inputs. | `users_v2` (updates `is_email_verified`, clears OTP fields). |
| **Create First Admin** / **First Admin Setup** | `create-first-admin.html` / `setup-admin.html` | First Name, Last Name, Email, Password Inputs. | `users_v2` (inserts initial admin account), `user_roles` (role assignment). |
| **Server Health Check** | `server-health.html` | Health Check Token input. Buttons: Check Health, Setup Database, Show Schema. Displays: Logs status, DB schema tables/columns/constraints. | Dynamically queries database schema information (`INFORMATION_SCHEMA.COLUMNS`, `INFORMATION_SCHEMA.KEY_COLUMN_USAGE`) and runs init scripts (`sql/*.sql`). |
### Trace Summary for Primary Entities:
* **Client Core Information:** Traces from `client-personal-details-form.html` labels to the frontend `POST /api/client/personal-details` call. The backend handler in `clientRoutes.mjs` maps these fields to the `users_v2` table (for fixed identity data) and the `client_consultations` table (for variables that change per consultation, like weight or diet).
* **Blood Test Data:** Field IDs on `client-blood-tests-form.html` match the `test_code` logic in `bloodTestDefinitions.js`. These are submitted to `POST /api/client/blood-tests`, where the backend inserts a parent record into `client_blood_test_reports` and multiple child records into `client_blood_test_results`.
* **Hourly Food Plan:** The grid in `client-food-plan.html` uses a dynamic hourly mapping. API calls through `/api/client/food-plan` result in data storage within `client_food_plans` (for metadata) and `client_food_plan_hourly_details` (for specific entries per `time_slot`).
* **Staff Assignment Flow:** Traces from selection on `client-select-staff.html` to frontend `POST /api/client/update-staff-preference` call. The backend handler in `clientRoutes.mjs` writes the assigned IDs directly into the client's record in the `users_v2` table.
* **Consultation History Comparison Flow:** Traces from `compare.html` (via `js/compare.js`) to the `GET /api/client/compare/:clientId` endpoint. The backend handler in `clientRoutes.mjs` joins and aggregates the client's historical data across `client_consultations`, `client_blood_test_reports`/`results`, `client_medical_history`/`medications`, and `client_food_plans`/`hourly_details` to return a chronological dataset.
* **Right to Erasure (Account Deletion) Flow:** Traces from the client dashboard (`client-dashboard.html` / `DELETE /api/client/me`) or admin console (`admin.html` / `DELETE /api/admin/clients/:clientId`). Both execute a transaction in `clientRoutes.mjs` or `adminRoutes.mjs` that permanently hard-deletes records in `users_v2`, `user_roles`, `client_consultations`, `client_blood_test_reports`, `client_blood_test_results`, `client_medical_history`, `client_medications`, `client_food_plans`, and `client_food_plan_hourly_details` to ensure legal compliance.
* **Self-Service Password Reset Flow:** Traces email submission from `forgot-password.html` to `POST /api/forgot-password` (generating a token and expiry in `users_v2`) and password reset form submission in `reset-password.html` to `POST /api/reset-password` (which validates the token, hashes the new password, updates `users_v2`, and clears reset fields).
--- README.md ---
# consultation
## Test Users
https://sevenpointfour.in/consulttest/staff-login.html
- Nutritionist: `arogyanubhutifoundation@gmail.com`; `111111`
- Executive: `arogyanubhutitraining@gmail.com`; `222222`
https://sevenpointfour.in/consulttest/admin-login.html
- Admin: `madhavjoshi02@gmail.com`; `123456`
https://sevenpointfour.in/consulttest/register.html
https://sevenpointfour.in/consulttest/client-login.html
https://sevenpointfour.in/consulttest/server-health.html
- server-health-check-74
--- public/js/compare.js ---
/**
* Custom error class for handling authentication/authorization issues.
*/
class AuthError extends Error {
constructor(message, tokenType) {
super(message);
this.name = 'AuthError';
this.tokenType = tokenType; // 'client', 'admin', 'staff', or 'none'
}
}
/**
* Determines the user type based on available tokens.
* @returns {string} 'admin', 'staff', 'client', or 'none'
*/
function getUserType() {
const role = (localStorage.getItem('selectedRole') || '').toLowerCase();
if (role === 'admin') return 'admin';
if (role === 'nutritionist' || role === 'executive') return 'staff';
const urlParams = new URLSearchParams(window.location.search);
const clientId = urlParams.get('client_id') || urlParams.get('clientId');
if (clientId === 'me') return 'client';
return 'none';
}
/**
* Handles user logout by clearing the appropriate token and redirecting.
* @param {string} userType - 'admin', 'staff', or 'client'
*/
function handleLogout(userType) {
const loginPage = 'login.html';
fetch('/api/logout', { method: 'POST' }).finally(() => {
localStorage.removeItem('selectedRole');
window.location.href = loginPage;
});
}
/**
* Renders the appropriate navigation bar based on the user type.
*/
function renderNavbar() {
const userType = getUserType();
const navbarContainer = document.getElementById('navbar-placeholder');
if (!navbarContainer) return;
let navLinks = '';
let brandText = 'Dashboard';
let brandLink = '#';
const urlParams = new URLSearchParams(window.location.search);
const clientId = urlParams.get('client_id') || urlParams.get('clientId');
switch (userType) {
case 'admin':
brandText = 'Back to Admin Dashboard';
brandLink = 'admin.html';
navLinks = clientId ? `Back to Client List` : ``;
break;
case 'staff':
const role = localStorage.getItem('selectedRole');
brandLink = role === 'nutritionist' ? 'nutritionist-dashboard.html' : 'executive-dashboard.html';
brandText = role === 'nutritionist' ? 'Back to Nutritionist Dashboard' : 'Back to Executive Dashboard';
navLinks = clientId ? `My Clients` : `My Clients`;
break;
case 'client':
brandText = 'Back to Client Dashboard';
brandLink = 'client-dashboard.html';
navLinks = `Personal DetailsBlood TestsFood PlanMedical HistoryInitiate Follow-upHistoryWebsite`;
break;
default:
return; // No navbar for unknown users
}
const navbarHtml = `
`;
navbarContainer.innerHTML = navbarHtml;
const logoutButton = document.getElementById('logoutButton');
if (logoutButton) {
logoutButton.addEventListener('click', () => handleLogout(userType));
}
}
/**
* Fetches the comparison data for a client.
* It determines the correct authentication token to use based on the user type.
*/
async function fetchData() {
const urlParams = new URLSearchParams(window.location.search);
const clientId = urlParams.get('client_id') || urlParams.get('clientId');
if (!clientId) {
throw new AuthError("No client ID found in URL. Please ensure the URL is correct (e.g., compare.html?client_id=123).", 'none');
}
const res = await apiFetch(`/api/client/compare/${clientId}`);
if (!res.ok) {
const errorData = await res.json().catch(() => ({}));
const errorMessage = errorData.message || `Server returned an error (Status: ${res.status})`;
throw new Error(`Failed to fetch data: ${errorMessage}`);
}
return await res.json();
}
let fetchedData = null; // Store data to avoid refetching on tab switch
async function render() {
renderNavbar();
// Add custom styles for different sections to improve readability
const style = document.createElement('style');
style.textContent = `
.present-intake-row { background-color: #d1ecf1; } /* Light blue - darker shade */
.proposed-structure-row { background-color: #d4edda; } /* Light green - darker shade */
.additional-points-row { background-color: #fff3cd; } /* Light yellow - darker shade */
`;
document.head.appendChild(style);
// Update page title if being viewed by an admin or staff for a specific client
const urlParams = new URLSearchParams(window.location.search);
const clientIdParam = urlParams.get('client_id') || urlParams.get('clientId');
if (getUserType() !== 'client' && clientIdParam && clientIdParam !== 'me') {
document.getElementById('page-title').textContent = `Consultation History for Client ID: ${clientIdParam}`;
try {
const userType = getUserType();
const res = await apiFetch(`/api/${userType}/clients/${clientIdParam}/personal-details`);
const details = await res.json();
if (details.first_name) {
document.getElementById('page-title').textContent = `Consultation History for ${details.first_name} ${details.last_name} (ID: ${clientIdParam})`;
}
} catch (e) { console.error("Failed to fetch client name for title:", e); }
}
const container = document.getElementById('pivot-table-container');
const showMessage = (message, type = 'info') => {
container.innerHTML = `${message}
`;
};
// Tab Switching Logic
const tabBloodReports = document.getElementById('tab-blood-reports');
const tabFoodPlan = document.getElementById('tab-food-plan');
let activeTab = 'blood-reports';
if (tabBloodReports && tabFoodPlan) {
console.log('Tabs found. Attaching listeners.');
tabBloodReports.addEventListener('click', () => {
console.log('Switching to Blood Reports');
activeTab = 'blood-reports';
tabBloodReports.classList.add('active');
tabFoodPlan.classList.remove('active');
if (fetchedData) renderTable(fetchedData, activeTab);
});
tabFoodPlan.addEventListener('click', () => {
console.log('Switching to Food Plan');
activeTab = 'food-plan';
tabFoodPlan.classList.add('active');
tabBloodReports.classList.remove('active');
if (fetchedData) renderTable(fetchedData, activeTab);
});
} else {
console.error('Tabs NOT found. IDs: tab-blood-reports, tab-food-plan');
}
showMessage('Loading follow-up history...', 'info');
try {
const data = await fetchData();
if (!data || data.length === 0) {
showMessage('No follow-up history found for this client.', 'info');
return;
}
// --- Data Processing (Sorting) ---
const dateKeys = [
'consultation_date',
'date',
'follow_up_date',
'created_at',
'updated_at',
'timestamp',
'consultationDate',
'followupDate',
'dateOfConsultation'
];
const usedDateKey = dateKeys.find(key => data.some(item => item[key]));
if (usedDateKey) {
data.sort((a, b) => {
let dateAVal = a[usedDateKey];
let dateBVal = b[usedDateKey];
if (typeof dateAVal === 'string' && dateAVal.startsWith('"') && dateAVal.endsWith('"')) {
dateAVal = dateAVal.slice(1, -1);
}
if (typeof dateBVal === 'string' && dateBVal.startsWith('"') && dateBVal.endsWith('"')) {
dateBVal = dateBVal.slice(1, -1);
}
const dateA = new Date(dateAVal);
const dateB = new Date(dateBVal);
if (isNaN(dateA.getTime())) return -1; // Invalid dates go first
if (isNaN(dateB.getTime())) return 1;
return dateA - dateB;
});
}
// Store processed data
fetchedData = data;
fetchedData.usedDateKey = usedDateKey; // Attach key for use in renderTable
// Initial Render
renderTable(fetchedData, activeTab);
} catch (error) {
if (error instanceof AuthError) {
showMessage(`${error.message} Redirecting...`, 'error');
setTimeout(() => {
handleLogout(error.tokenType);
}, 2500);
} else {
console.error('Error rendering follow-up data:', error);
showMessage(error.message, 'error');
}
}
}
function renderTable(data, activeTab) {
const container = document.getElementById('pivot-table-container');
container.innerHTML = '';
const table = document.createElement('table');
table.classList.add('pivot-table');
const thead = document.createElement('thead');
const tbody = document.createElement('tbody');
const headerRow = document.createElement('tr');
// 1. Property Header
const propertyHeader = document.createElement('th');
propertyHeader.textContent = 'Property';
headerRow.appendChild(propertyHeader);
// 2. Range Header (Only for Blood Reports)
if (activeTab === 'blood-reports') {
const rangeHeader = document.createElement('th');
rangeHeader.textContent = 'Normal Range';
headerRow.appendChild(rangeHeader);
}
// 3. Date Headers
const usedDateKey = data.usedDateKey;
const columnHeaders = data.map((item, index) => {
const bloodTestDate = item['blood_test_results_report_date'];
let dateValue = bloodTestDate || item['consultation_date'] || (usedDateKey ? item[usedDateKey] : null);
if (dateValue) {
try {
if (typeof dateValue === 'string' && dateValue.startsWith('"') && dateValue.endsWith('"')) dateValue = dateValue.slice(1, -1);
let d = null;
const dateParts = typeof dateValue === 'string' ? dateValue.trim().match(/^(\d{1,2})[-/](\d{1,2})[-/](\d{2,4})/) : null;
const shortDateParts = typeof dateValue === 'string' ? dateValue.trim().match(/^(\d{2})(\d{2})(\d{2})$/) : null;
if (dateParts) {
let year = parseInt(dateParts[3], 10);
if (year < 100) year += 2000;
d = new Date(year, parseInt(dateParts[2], 10) - 1, parseInt(dateParts[1], 10));
} else if (shortDateParts) {
d = new Date(2000 + parseInt(shortDateParts[3], 10), parseInt(shortDateParts[2], 10) - 1, parseInt(shortDateParts[1], 10));
} else {
d = new Date(dateValue);
}
if (d && !isNaN(d.getTime())) {
const dateString = d.toLocaleDateString('en-GB', { day: '2-digit', month: 'short', year: 'numeric' });
return index === 0 ? `Initial (${dateString})` : `Follow-up ${index} (${dateString})`;
}
} catch (e) { console.error(`Error parsing date: '${dateValue}'`, e); }
}
return index === 0 ? 'Initial' : `Follow-up ${index}`;
});
columnHeaders.forEach(headerText => {
const th = document.createElement('th');
th.textContent = headerText;
headerRow.appendChild(th);
});
thead.appendChild(headerRow);
table.appendChild(thead);
// Helper to create rows
const createRow = (propKey, displayName, isHtml = false, className = null) => {
if (data.some(item => item.hasOwnProperty(propKey)) || className) {
const row = tbody.insertRow();
if (className) row.classList.add(className);
const propNameCell = row.insertCell();
propNameCell.textContent = displayName;
if (activeTab === 'blood-reports') {
const rangeCell = row.insertCell();
rangeCell.textContent = '';
}
data.forEach(item => {
const valueCell = row.insertCell();
const value = item[propKey] !== undefined && item[propKey] !== null ? item[propKey] : '';
if (isHtml) valueCell.innerHTML = value;
else valueCell.textContent = value;
});
}
};
// --- Render Content Based on Tab ---
if (activeTab === 'blood-reports') {
for (const sectionKey in bloodTestDefinitions) {
const section = bloodTestDefinitions[sectionKey];
const sectionTitleRow = tbody.insertRow();
sectionTitleRow.innerHTML = `${section.title} | `;
section.tests.forEach(testDef => {
const propKey = `blood_test_results_value_${testDef.key}`;
const row = tbody.insertRow();
const propNameCell = row.insertCell();
propNameCell.textContent = testDef.name;
const rangeCell = row.insertCell();
rangeCell.textContent = testDef.range || '';
data.forEach(item => {
const valueCell = row.insertCell();
valueCell.textContent = item[propKey] !== undefined && item[propKey] !== null ? item[propKey] : '';
});
});
}
} else if (activeTab === 'food-plan') {
// 1. Notes
const conversationSectionTitleRow = tbody.insertRow();
conversationSectionTitleRow.innerHTML = `Client & Nutritionist Notes | `;
createRow('health_issues', 'Health Details');
createRow('additional_personal_recommendations', 'Nutritionist Personal Recommendations', true);
// 2. Food Plan Details
const foodPlanSectionTitleRow = tbody.insertRow();
foodPlanSectionTitleRow.innerHTML = `Food Plan Details | `;
const hours = [];
for (let i = 6; i < 24; i++) hours.push(i);
for (let i = 0; i < 6; i++) hours.push(i);
// Present Intake
const presentIntakeSubHeader = tbody.insertRow();
presentIntakeSubHeader.innerHTML = `Present Intake | `;
hours.forEach(hour => {
const hourString = hour.toString().padStart(2, '0');
const propKey = `food_plan_hourly_details_present_intake_${hourString}:00`;
createRow(propKey, `${hourString}:00`, false, 'present-intake-row');
});
// Proposed Structure
const proposedStructureSubHeader = tbody.insertRow();
proposedStructureSubHeader.innerHTML = `Proposed Structure | `;
hours.forEach(hour => {
const hourString = hour.toString().padStart(2, '0');
const propKey = `food_plan_hourly_details_proposed_structure_${hourString}:00`;
createRow(propKey, `${hourString}:00`, false, 'proposed-structure-row');
});
// Additional Points
const additionalPointsSectionTitleRow = tbody.insertRow();
additionalPointsSectionTitleRow.innerHTML = `Additional Points | `;
hours.forEach(hour => {
const hourString = hour.toString().padStart(2, '0');
const propKey = `food_plan_hourly_details_additional_points_${hourString}:00`;
createRow(propKey, `${hourString}:00`, false, 'additional-points-row');
});
}
table.appendChild(tbody);
container.appendChild(table);
}
document.addEventListener('DOMContentLoaded', render);
--- public/js/client-dashboard.js ---
document.addEventListener('DOMContentLoaded', async () => {
const messageDiv = document.getElementById('message');
const clientInfoSection = document.getElementById('clientInfoSection');
const clientNameSpan = document.getElementById('clientName');
const clientEmailSpan = document.getElementById('clientEmail');
const clientIdSpan = document.getElementById('clientId');
try {
const response = await apiFetch('api/client/me');
if (!response.ok) {
const errorResult = await response.json().catch(() => ({ error: `Server error: ${response.status}` }));
throw new Error(errorResult.error || `HTTP error! status: ${response.status}`);
}
const clientData = await response.json();
clientNameSpan.textContent = `${clientData.first_name} ${clientData.last_name}`;
clientEmailSpan.textContent = clientData.email;
clientIdSpan.textContent = clientData.client_id;
clientInfoSection.style.display = 'block';
} catch (error) {
if (error.message !== 'SESSION_EXPIRED') {
console.error('Error fetching client data:', error);
messageDiv.textContent = `Error loading dashboard: ${error.message}. Please try logging in again.`;
messageDiv.style.color = 'red';
}
}
const deleteAccountBtn = document.getElementById('deleteAccountBtn');
if (deleteAccountBtn) {
deleteAccountBtn.addEventListener('click', async () => {
if (confirm('Are you sure you want to permanently delete your account and all associated data? This action cannot be undone.')) {
try {
const response = await apiFetch('api/client/me', { method: 'DELETE' });
if (response.ok) {
alert('Your account has been successfully deleted.');
// Trigger logout to clear cookies
await fetch('/api/logout', { method: 'POST' });
window.location.href = 'login.html';
} else {
const result = await response.json();
alert('Error: ' + (result.error || 'Failed to delete account.'));
}
} catch (error) {
console.error('Error deleting account:', error);
alert('An error occurred. Please try again.');
}
}
});
}
});
--- public/js/bloodTestDefinitions.js ---
// Define the test structure for the admin view (similar to client-blood-tests-form.html)
const bloodTestDefinitions = {
cbc: {
title: 'COMPLETE BLOOD COUNT (CBC)',
tests: [
{ key: 'hemoglobin', name: 'HEMOGLOBIN', range: 'M= 13.5-18 GM/DL; F=12-16.8 GM/DL' },
{ key: 'total_wbc', name: 'TOTAL WBC', range: '4000-11000 /CMM' },
{ key: 'total_rbc', name: 'TOTAL RBC', range: '4.5-6 MIL/CMM' },
{ key: 'platelet', name: 'PLATELET', range: '150000-450000 /CMM' },
{ key: 'pcv', name: 'PCV', range: 'M - 40 TO 50 , F - 36 TO 46' },
{ key: 'mcv', name: 'MCV', range: '83 TO 101 FL' },
{ key: 'mch', name: 'MCH', range: '27-32 PG' },
{ key: 'mchc', name: 'MCHC', range: '32-36% G/DL' },
{ key: 'rdw', name: 'RDW', range: '11.5-14%' },
{ key: 'eosinophils', name: 'EOSINOPHILS', range: '0 - 6' }
]
},
diabetes: {
title: 'DIABETES INDICATORS',
tests: [
{ key: 'fasting_glucose', name: 'FASTING BLOOD GLUCOSE', range: '70-110 MG/DL' },
{ key: 'fasting_insulin', name: 'FASTING INSULIN', range: '2.6-37.6 MICRO U/ML' },
{ key: 'hba1c_hplc', name: 'HbA 1 C (HPLC)', range: '</= 6.0 % OF TOTAL Hb' },
{ key: 'hba1c_ifcc', name: 'HbA 1 C (IFCC) (HPLC)', range: '</= 42MMOL/MOL' },
{ key: 'avg_plasma_glucose', name: 'AVG. PLASMA GLUCOSE OF LAST 3 MONTHS (CALC)', range: '80-140 MG/DL' }
]
},
kft: {
title: 'KIDNEY FUNCTION TESTS (KFT)',
tests: [
{ key: 'uric_acid', name: 'URIC ACID', range: 'M-3.5 TO 7.2, F - 2.6 TO 6' },
{ key: 'bun', name: 'BLOOD UREA NITROGEN ( BUN)', range: 'MG/DL 7.9 TO 20' },
{ key: 's_creatinine', name: 'S. CREATININE', range: 'M= 0.4-1.4 MG/DL; F= 0.2-1.2 MG/DL' },
{ key: 'bun_creatinine_ratio', name: 'BUN / S CREATININE RATIO', range: '9.1 TO 23.1' },
{ key: 'sodium', name: 'SODIUM', range: 'MMOL/L 136-146' },
{ key: 'chloride', name: 'CHLORIDE', range: 'MMOL/L 98 - 106' },
{ key: 'egfr', name: 'ESTIMATED GLOMERULAR FILTERATION RATE', range: '>90, 60-90,45-59,30 -44, 15,29' }
]
},
lft: {
title: 'LIVER FUNCTION TESTS (LFT)',
tests: [
{ key: 'bilirubin_total', name: 'S. BILIRUBIN-TOTAL', range: 'UP TO 1.2 MG/DL' },
{ key: 'bilirubin_direct', name: 'S. BILIRUBIN-DIRECT', range: 'UP TO 0.4 MG/DL' },
{ key: 'bilirubin_indirect', name: 'S. BILIRUBIN-INDIRECT', range: '0.1-1.0 MG/DL' },
{ key: 'sgpt_alt', name: 'S.G.P.T.( ALT)', range: 'UP TO 40 U/L ( M - 13 TO 40 , F - 10 TO 28)' },
{ key: 'sgot', name: 'S.G.O.T.', range: 'M - 0-37, F - 0 -31' },
{ key: 's_alkaline_phosphatase', name: 'S. ALKALINE PHOSPHATASE', range: '40-129 U/L' },
{ key: 'protein', name: 'PROTEIN', range: 'GM/DL 5.7 TO 8.2' },
{ key: 'albumin', name: 'ALBUMIN', range: 'GM/DL 3.2 TO 4.8' },
{ key: 'globulin', name: 'GLOBULIN', range: 'GM/DL 2,5 TO 3.4' },
{ key: 'ag_ratio', name: 'ABUMIN/ GLOBULIN RATIO', range: '0.9 TO 2.0' }
]
},
lipid: {
title: 'LIPID PROFILE',
tests: [
{ key: 'cholesterol', name: 'S.CHOLESTEROL', range: '125-200 MG/DL' },
{ key: 'triglyceride', name: 'S.TRIGLYCERIDE', range: '<150' },
{ key: 'hdl', name: 'S.HDL', range: '35-70 MG/DL' },
{ key: 'ldl', name: 'S.LDL', range: '<100 MG%' },
{ key: 'vldl', name: 'S.VLDL', range: '15-35 MG/DL' },
{ key: 'ldl_hdl_ratio', name: 'LDL / HDL RATIO', range: '<3' },
{ key: 'chol_hdl_ratio', name: 'TOTAL CHOLESTEROL/HDL', range: '<3.5' },
{ key: 'homocystin', name: 'HOMOCYSTEINE', range: '1.0-15.39 UMOL/L ( >30 RISK )' }
]
},
inflammation: {
title: 'INFLAMMATION MARKERS',
tests: [
{ key: 'crp', name: 'CRP', range: 'UP TO 6 MG/DL ( <1, 1 TO 3 AND >3)' }
]
},
vitamind3: {
title: 'VITAMIN D3',
tests: [
{ key: 'vit_d3', name: '25 OH CHOLECALCIFEROL (D2+D3) (CMIA)', range: '30-100 NG/ML' }
]
},
vitaminb12: {
title: 'VITAMIN B12',
tests: [
{ key: 'vit_b12', name: 'S. VITAMIN B12', range: '211-911 PG/ML' }
]
},
testosterone: {
title: 'TESTOSTERONE',
tests: [
{ key: 'testosterone', name: 'S. TESTOSTERONE', range: 'M=249-836 NG/DL, F=8-60 NG/DL' }
]
},
thyroid: {
title: 'THYROID FUNCTION TEST',
tests: [
{ key: 't3', name: 'T3', range: 'NG/DL 60 TO 200' },
{ key: 't4', name: 'T4', range: 'MUG/DL 4.5 TO 12' },
{ key: 'tsh', name: 'TSH', range: 'MIU/ML 0.3 TO 5.5' }
]
}
};
function buildReportsTable(reportData) {
// Use a Map for efficient lookup of test results by test_code
const resultsMap = new Map((reportData.results || []).map(r => [r.test_code, r]));
console.log("Results Map created, size:", resultsMap.size);
// Create the table structure
const table = document.createElement('table');
table.id = 'bloodTestsTable'; // For CSS targeting
const thead = document.createElement('thead');
const tbody = document.createElement('tbody');
table.appendChild(thead);
table.appendChild(tbody);
// Populate the main generic header
const mainHeaderRow = thead.insertRow();
mainHeaderRow.innerHTML = `
Test Name |
Normal Range |
Date 1 |
Date 2 |
Date 3 |
Date 4 |
Date 5 |
`;
let cbcSubHeaderAdded = false; // Flag to ensure CBC subheader is added only once
// Iterate through defined test sections and tests
for (const sectionKey in bloodTestDefinitions) {
const section = bloodTestDefinitions[sectionKey];
// Add CBC specific subheader if this is the CBC section and it hasn't been added
if (sectionKey === 'cbc' && !cbcSubHeaderAdded) {
const cbcHeaderRow = tbody.insertRow();
cbcHeaderRow.classList.add('cbc-admin-subheader-row'); // Use the correct CSS class
let cbcSubHeaderHTML = `${escapeHtml(section.title)} | `; // Title spans 2 columns
// Add date cells with specific DD/MM/YY format
const dateValue = reportData[`report_date`];
const formattedDate = dateValue
? (() => {
const d = new Date(dateValue);
const year = String(d.getFullYear()).slice(-2);
const month = String(d.getMonth() + 1).padStart(2, '0');
const day = String(d.getDate()).padStart(2, '0');
return escapeHtml(`${day}/${month}/${year}`); // DD/MM/YY format
})()
: ' '; // Use for empty dates
cbcSubHeaderHTML += `${formattedDate} | `;
cbcHeaderRow.innerHTML = cbcSubHeaderHTML; // Assign the complete HTML string
cbcSubHeaderAdded = true; // Set flag
} else if (sectionKey !== 'cbc') { // Only add generic title row for non-CBC sections
// Add section title row
const sectionTitleRow = tbody.insertRow();
sectionTitleRow.classList.add('test-section-title-row'); // Use existing class for styling
const titleCell = sectionTitleRow.insertCell();
titleCell.colSpan = 7; // Span all 7 columns
titleCell.outerHTML = `${escapeHtml(section.title)} | `; // Use th for section titles
}
// Add individual test rows
section.tests.forEach(testDef => {
const clientResult = resultsMap.get(testDef.key); // Efficiently get the result for this test
const testRow = tbody.insertRow();
testRow.innerHTML = `
${escapeHtml(testDef.name)} |
${testDef.range ? escapeHtml(testDef.range) : 'N/A'} |
${clientResult?.value ?? 'N/A'} |
${clientResult?.value_d2 ?? 'N/A'} |
${clientResult?.value_d3 ?? 'N/A'} |
${clientResult?.value_d4 ?? 'N/A'} |
${clientResult?.value_d5 ?? 'N/A'} |
`;
});
}
return table;
}
// Helper function to escape HTML for security
function escapeHtml(unsafe) {
if (typeof unsafe !== 'string') return unsafe; // Return non-strings as is
return unsafe
.replace(/&/g, "&")
.replace(//g, ">")
.replace(/"/g, """)
.replace(/'/g, "'");
}