File: /home/mmtprep/server.js
const express = require("express");
const mysql = require('mysql2');
const cors = require('cors');
const session = require('express-session');
const fs1 = require('fs');
const fs = require('fs').promises;
const constants = require('fs').constants;
const path = require('path');
const db = require("./db/db");
const morgan = require("morgan");
const conversationServices = require("./services/conversationServices");
const nodemailer = require('nodemailer');
const winston = require('winston');
const { v4: uuidV4 } = require('uuid'); //making classid
const { v4: createRoomID } = require('uuid'); //making classid
const { Server } = require("socket.io");
const fileupload = require("express-fileupload");
const bodyParser = require('body-parser');
const port = process.env.PORT || 8080; //remove
const util = require('util');
const moment = require('moment');
const { promisify } = require('util');
const hashMap = new Map();
const queryAsync = promisify(db.query).bind(db);
const axios = require('axios');
const Groq = require('groq-sdk').default;
const crypto = require('crypto');
const app = express();
app.use(morgan("tiny"));
// 데이터베이스에서 데이터를 로드하고 해시맵을 초기화
async function initializeHashMap() {
const results = await queryAsync('SELECT rid, id FROM class_session_objects');
results.forEach(row => {
hashMap.set(row.id, row.rid);
});
}
app.use(session({
secret: '_lhvudxs%wfhb-ks2vh1l+_g&y)3rw$338d)ia4j&gf&^e_y-=',
cookie: { secure: false },
resave: false,
saveUninitialized: true
}));
const logFile = fs1.createWriteStream('app.log');
console.log = function () {
logFile.write(util.format.apply(null, arguments) + '\n');
process.stdout.write(util.format.apply(null, arguments) + '\n');
};
console.error = function () {
logFile.write(util.format.apply(null, arguments) + '\n');
process.stderr.write(util.format.apply(null, arguments) + '\n');
};
const logger = winston.createLogger({
level: 'info',
format: winston.format.json(),
defaultMeta: { service: 'user-service' },
transports: [
new winston.transports.File({ filename: 'error.log', level: 'error' }),
new winston.transports.File({ filename: 'combined.log' }),
],
});
logger.add(new winston.transports.Console());
let transporter = nodemailer.createTransport({
host: "donotemail.mmtprep.com",
port: 465,
secure: true, // true for 465, false for other ports
auth: {
user: "donotreply@donotemail.mmtprep.com", // generated ethereal user
pass: "Mmtmmt1234!", // generated ethereal password
},
});
if (process.env.NODE_ENV !== 'production') {
logger.add(new winston.transports.Console({
format: winston.format.simple(),
}));
}
process.on('uncaughtException', async (err) => {
logger.error(`Uncaught Exception: ${err.message}`);
logger.on('finish', async () => {
const logContent = await fs1.readFile('./error.log', 'utf8');
await transporter.sendMail({
from: 'donotreply@donotemail.mmtprep.com',
to: 'aiden1393@gmail.com',
subject: 'Server crashed',
text: `The server has crashed. Error: ${err.message}\n\nLogs:\n${logContent}`,
attachments: [
{
filename: 'app.log',
path: './app.log' // replace with the path to your log file
}
]
});
process.exit(1); // exit process
});
logger.end();
});
const accountSid = "AC336880183d60acecdbe6b3effd0cdb3b";
const authToken = "523633192e3e123bfdc4cffa671c3063";
const client = require('twilio')(accountSid, authToken);
let iceServers;
client.tokens.create().then((token) => {
iceServers = [{ urls: 'stun:stun.l.google.com:19302' }, ...token.iceServers];
}).catch((err) => {
console.log(err);
});
app.use(cors({ credentials: true, origin: ['https://accounts.google.com', 'http://localhost:5173', 'https://192.168.0.240:5173', 'http://192.168.0.240:5173', '192.168.0.240:5173'] }));
app.use(fileupload());
app.use(express.static("files"));
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ limit: '50mb', extended: true }));
app.use(bodyParser.urlencoded({ extended: true }));
app.use('/blog', require('./routes/blog_route'));
app.use('/email', require('./routes/email_route'));
app.use('/invoice', require('./routes/invoice_route'));
app.use('/dashboard', require('./routes/dashboard_route'));
app.use('/conversation', require('./routes/conversation_route'));
app.use('/message', require("./routes/message_routes"))
app.use('/uploads', express.static('./uploads'));
app.use('/summer', require('./routes/summer'));
app.use("/classsessions", require('./routes/class_session_router'));
app.use('/auth', (req, res, next) => {
res.removeHeader("Cross-Origin-Opener-Policy");
res.removeHeader("Cross-Origin-Embedder-Policy");
next();
});
app.get('/api/calendar/events', async (req, res) => {
const { accessToken, calendarId } = req.query;
if (!accessToken || !calendarId) {
return res.status(400).json({ error: 'Access token and calendar ID are required.' });
}
try {
const response = await axios.get(`https://www.googleapis.com/calendar/v3/calendars/${encodeURIComponent(calendarId)}/events`, {
headers: {
Authorization: `Bearer ${accessToken}`,
},
params: {
singleEvents: true,
orderBy: 'startTime',
timeMin: new Date().toISOString(),
maxResults: 100,
},
});
res.json(response.data);
} catch (error) {
console.error('Error fetching events:', error.response.data);
res.status(error.response.status).json(error.response.data);
}
});
/*const privateKey = fs1.readFileSync('./ssl/certificate.key', 'utf8');
const certificate = fs1.readFileSync('./ssl/certificate.crt', 'utf8');
const ca = fs1.readFileSync('./ssl/certificate.ca', 'utf8'); // Optional Certificate Authority (CA) bundle
const credentials = {
key: privateKey,
cert: certificate,
ca: ca
};*/
const server = require('http').Server(app);
//var basic = `https://api.promaxpdx.com`;
var basic = `http://localhost:8080`;
const io = new Server(server, {
cors: {
origin: "*",
methods: ["GET", "POST"],
},
maxHttpBufferSize: 1e7 // 10 MB (adjust as needed)
});
function getRandomInt() {
const userId = uuidV4()
return userId;
}
function getRandomInt3() {
return Math.floor(Math.random() * 900) + 99;
}
let users = [];
let calls = [];
let whiteboardSessions = [];
const socketIdToCSIdMap = {};
const addUser = (userId, socketId) => {
users = users.filter((user) => user.userId !== userId);
users.push({ userId: userId, socketId: socketId });
}
const removeUser = (socketId) => {
users = users.filter((user) => user.socketId !== socketId);
}
const findUser = (userId) => {
return users.find(user => user.userId === userId);
}
const findUserBySocketId = (socketId) => {
return users.find(user => user.socketId === socketId);
}
const findCall = (callId) => {
return calls.find(call => call.callId === callId);
}
const findCallByConversationId = (conversationId) => {
return calls.find(call => call.conversationId == conversationId);
}
io.on('connection', (socket) => {
socket.on('addUser', (userId) => {
addUser(userId, socket.id);
io.emit('getUsers', users);
})
socket.on('ping', ({ roomId, userId }) => {
const session = whiteboardSessions.find(s => s.roomId === roomId);
if (session) {
const user = session.joinedUsers.find(u => u.userId === userId);
if (user) {
user.lastPing = Date.now(); // Update the last ping time
// Optionally, respond immediately with the active users
socket.emit('ping-response', { activeUsers: session.joinedUsers });
}
}
});
socket.on('disconnect', () => {
const user = findUserBySocketId(socket.id);
if (user) {
socket.broadcast.to(user.roomId).emit('leave', { name: user.name });
}
removeUser(socket.id);
setTimeout(() => {
io.emit('getUsers', users);
}, 750)
})
socket.on('manual-disconnect', ({ roomId, userId }) => {
const session = whiteboardSessions.find(s => s.roomId === roomId);
if (session) {
const userIndex = session.joinedUsers.findIndex(u => u.userId === userId);
if (userIndex !== -1) {
const [removedUser] = session.joinedUsers.splice(userIndex, 1);
// Notify other users in the room
io.to(roomId).emit('user-left', { name: removedUser.name, userId: removedUser.userId });
// Emit updated active users list
io.to(roomId).emit('ping-response', { activeUsers: session.joinedUsers });
}
}
});
socket.on('viewport-area', (data) => {
if (!data.roomId) return;
// 동일한 방에 있는 다른 모든 클라이언트에게 전송
socket.to(data.roomId).emit('viewport-area', {
userId: data.userId,
realUserId: data.realUserId,
name: data.name,
viewport: data.viewport,
screenSize: data.screenSize,
timestamp: data.timestamp
});
});
socket.on('sendMessage', ({ mid, cs_id, uid, content, attachments, created_at, receiverId }) => {
console.log('sendMessage', { uid, created_at, content });
let user = findUser(receiverId);
socket.to(user?.socketId).emit('receiveMessage', { mid, cs_id, uid, content, attachments, created_at })
})
socket.on('newConversation', (data) => {
let user = findUser(data?.to);
socket.to(user?.socketId).emit('newConversation', data);
});
socket.on('callUser', (data) => {
console.log('callUser', data);
let user = findUser(data?.receiverId);
let call = findCall(data?.callId);
if (!call) {
call = { callId: data.callId, conversationId: data.conversationId, joinedMembers: [data?.callerId], groupName: data?.groupName };
calls.push(call);
}
data.iceServers = iceServers
socket.to(user?.socketId).emit('callUser', call);
const newCall = findCall(data.callId);
io.to(socketIdToCSIdMap[socket.id]).emit('getCallData', newCall);
});
socket.on('acceptedCall', (data) => {
console.log('acceptedCall', data);
let user = findUser(data?.callerId);
socket.to(user?.socketId).emit('acceptedCall', data);
});
socket.on('rejectedCall', (data) => {
let user = findUser(data?.callerId);
socket.to(user?.socketId).emit('rejectedCall', data);
});
socket.on('callEnded', (data) => {
let user = findUser(data?.to);
socket.to(user?.socketId).emit('callEnded', data);
});
socket.on("getAddedUsersByCallId", (data) => {
let call = findCall(data.callId);
let user = findUser(data.accepterId);
if (call) {
addUserInCall(data.callId, data.accepterId);
}
if (user) {
call.iceServers = iceServers;
io.to(user.socketId).emit("usersByCallId", call);
const newCall = findCall(data.callId);
io.to(socketIdToCSIdMap[socket.id]).emit('getCallData', newCall);
}
});
socket.on("joinUserInCall", (data) => {
let user = findUser(data?.receiverId);
data.iceServers = iceServers;
io.to(user?.socketId).emit('joinUserInCall', data);
});
socket.on('confirmAccepted', (data) => {
let user = findUser(data.peerSenderId);
io.to(user?.socketId).emit('confirmAccepted', data);
})
socket.on("leaveCall", (data) => {
let call = findCall(data.callId);
let mems = call?.joinedMembers.filter((mem) => mem !== data?.userId);
if (mems?.length === 0) {
calls = calls.filter((call) => call.callId !== data.callId);
} else {
calls = calls.map((call) => {
if (call.callId === data.callId) {
return { ...call, joinedMembers: mems };
} else {
return call;
}
})
}
mems?.forEach(mem => {
let user = findUser(mem);
io.to(user?.socketId).emit('userLeft', data);
// io.to(user?.socketId).emit('callUpdate',{msg: `${data.username} has left the call`} );
});
const newCall = findCall(data.callId);
io.to(socketIdToCSIdMap[socket.id]).emit('getCallData', newCall);
})
socket.on('joinConversation', (data) => {
console.log('joinConversation', data);
const csId = data.csId;
const prevRoom = socketIdToCSIdMap[socket.id];
if (prevRoom) {
socket.leave(prevRoom);
}
socketIdToCSIdMap[socket.id] = csId;
socket.join(csId);
// Broadcast call data to everyone
const user = findUserBySocketId(socket.id);
if (user) {
const call = findCallByConversationId(csId);
io.to(csId).emit('getCallData', call);
}
});
socket.on('request-user-location', ({ roomId, targetUserId }) => {
const session = whiteboardSessions.find((el) => el.roomId === roomId);
if (!session) return;
const targetUser = session.joinedUsers.find(u => u.userId === targetUserId);
if (targetUser) {
// targetUserSocketId를 targetUser.userId로 추적해야 할 수도 있습니다.
// userId가 socketId인지 확인 필요.
// 만약 userId가 곧 socket의 ID라면 targetUser.userId를 사용
const targetUserSocketId = targetUser.userId;
io.to(targetUserSocketId).emit('send-location-data', { requesterSocketId: socket.id });
}
});
socket.on('user-location-response', ({ viewPortTransform, zoomView, requesterSocketId }) => {
// 요청자에게 해당 transform, zoom 보내기
io.to(requesterSocketId).emit('other-user-location', { viewPortTransform, zoomView });
});
socket.on('join-room', ({ roomId, userId, name, realUserId, role }) => {
// 필수 데이터가 없는 경우, 이벤트 처리 중단
if (!userId || !name || !realUserId) {
console.warn('join-room 이벤트에 필요한 값이 없습니다:', { roomId, userId, name, realUserId, role });
return;
}
if (name.includes('undefined')) {
name = name.replace('undefined undefined', 'Anonymous User')
.replace('undefined', 'Anonymous');
}
console.log('join-room', { roomId, userId, name, realUserId, role });
let session = whiteboardSessions.find((el) => el.roomId === roomId);
console.log("=>session", session);
if (session) {
console.log("==> session");
let currentUser = session.joinedUsers.find((el) => el.realUserId === realUserId);
if (currentUser) {
session.joinedUsers = session.joinedUsers.map(el => {
if (el.realUserId === realUserId) {
return { name, userId, realUserId, lastPing: Date.now(), role }; // lastPing 갱신
} else {
return el;
}
});
} else {
session.joinedUsers.push({ name, userId, realUserId, lastPing: Date.now(), role });
}
let joinedUsers = session.joinedUsers;
io.to(userId).emit('joinedUserinwhiteboardSession', { joinedUsers });
whiteboardSessions = whiteboardSessions.map((s) => {
if (s.roomId === roomId) {
return session;
} else {
return s;
}
});
} else {
whiteboardSessions.push({ roomId, joinedUsers: [{ name, userId, realUserId, lastPing: Date.now(), role }] });
io.to(userId).emit('joinedUserinwhiteboardSession', { joinedUsers: [{ name, userId, realUserId, lastPing: Date.now(), role }] });
}
socket.join(roomId);
socket.broadcast.to(roomId).emit('new-user', { name, userId, realUserId });
});
socket.on('send-element', (data) => {
let q1 = `select * from canvas_sessions where roomId = "${data.roomId}"`;
db.query(q1, (err, result) => {
if (result[0]) {
let canR = JSON.stringify(data.canvasesRaw);
result[0].canvasesRaw = data.canvasesRaw;
let q2 = `update canvas_sessions set canvasesRaw = '${canR}' where roomId = "${data.roomId}";`
db.query(q2, (err, result2) => {
if (result) {
}
})
socket.broadcast.to(data.roomId).emit('recive-element', { ...result[0], canvasesRaw: data.canvasesRaw });
} else if (err) {
console.log(err);
}
})
});
socket.on('mirror', ({ roomId, width, height, zoom, scrollTop, scrollLeft, zoomPoint }) => {
socket.broadcast.to(roomId).emit('mirror', { width, height, zoom, scrollTop, scrollLeft, zoomPoint });
});
{/*
socket.on("room-viewPortTransform", async ({ roomId, viewPortTranform, zoomView, userId }) => {
socket.broadcast.to(roomId).emit("room-viewPortTransform", { roomId, viewPortTranform, zoomView, userId })
})
*/}
socket.on("room-viewPortTransform", ({ roomId, viewPortTranform, zoomView }) => {
socket.broadcast.to(roomId).emit("room-viewPortTransform", { viewPortTranform, zoomView });
});
socket.on("room-viewPortTransform-indie", async ({ roomId, type, event, userId, realUserId }) => {
let session = whiteboardSessions.find((el) => el.roomId === roomId);
if (session) {
let currentUser = session.joinedUsers.find((el) => el.realUserId === realUserId);
io.to(currentUser.userId).emit("room-viewPortTransform-indie", { roomId, type, event, userId })
io.to(userId).emit("room-viewPortTransform-indie", { roomId, type, event, userId })
}
})
socket.on("zoom", async ({ roomId, type, data, userId, realUserId }) => {
let session = whiteboardSessions.find((el) => el.roomId === roomId);
if (session) {
let currentUser = session.joinedUsers.find((el) => el.realUserId === realUserId);
io.to(currentUser.userId).emit("zoom", { roomId, type, data, userId })
io.to(userId).emit("zoom", { roomId, type, data, userId })
}
})
socket.on("lock-view", async ({ roomId, userId, realUserId, disabled }) => {
let session = whiteboardSessions.find((el) => el.roomId === roomId);
let currentUser = session.joinedUsers.find((el) => el.realUserId === realUserId);
io.to(currentUser.userId).emit("lock-view", { disabled })
io.to(userId).emit("lock-view", { disabled })
})
socket.on('user-coord', (data) => {
socket.broadcast.to(data.roomId).emit('user-coord', data);
})
socket.on('object-added', async ({ roomId, object, objectId }) => {
let connection;
try {
// 풀에서 연결 가져오기
connection = await new Promise((resolve, reject) => {
db.getConnection((err, conn) => {
if (err) reject(err);
else resolve(conn);
});
});
// 트랜잭션 시작
await new Promise((resolve, reject) => {
connection.beginTransaction(err => {
if (err) reject(err);
else resolve();
});
});
// cs_id 존재 여부 확인
const csExists = await new Promise((resolve, reject) => {
const checkQuery = `SELECT cs_id FROM class_sessions WHERE cs_id = ?`;
connection.query(checkQuery, [roomId], (err, results) => {
if (err) reject(err);
else resolve(results.length > 0);
});
});
if (!csExists) {
// cs_id가 없으면 트랜잭션 롤백
await new Promise((resolve, reject) => {
connection.rollback(() => {
resolve();
});
});
console.error(`Class session ID ${roomId} does not exist. Cannot add object.`);
return;
}
// 객체 형식 확인 및 문자열화
let objectString;
try {
objectString = typeof object === 'string'
? object
: JSON.stringify(object);
} catch (err) {
console.error('Error stringifying object:', err);
throw err;
}
// 오브젝트 삽입
const insertQuery = `INSERT INTO class_session_objects (id, cs_id, data) VALUES (?, ?, ?)`;
const result = await new Promise((resolve, reject) => {
connection.query(insertQuery, [objectId, roomId, objectString], (err, result) => {
if (err) reject(err);
else resolve(result);
});
});
// rid 설정 및 HashMap에 저장
const rid = result.insertId;
hashMap.set(objectId, rid);
// 트랜잭션 커밋
await new Promise((resolve, reject) => {
connection.commit(err => {
if (err) reject(err);
else resolve();
});
});
// 성공적으로 삽입 후 이벤트 브로드캐스트
socket.broadcast.to(roomId).emit('object-added', { roomId, object, objectId });
console.log(`Object ${objectId} added to room ${roomId} with rid ${rid}`);
} catch (err) {
console.error('Error in object-added event:', err);
// 트랜잭션 롤백
if (connection) {
try {
await new Promise((resolve, reject) => {
connection.rollback(() => {
resolve();
});
});
} catch (rollbackErr) {
console.error('Error during rollback:', rollbackErr);
}
}
} finally {
// 연결 반환
if (connection) connection.release();
}
});
socket.on('object-modified', async ({ roomId, objects }) => {
try {
// 언제나 배열로 맞춰주기
const list = Array.isArray(objects) ? objects : [objects];
// DB 업데이트 & 브로드캐스트용 객체 생성
const modified = [];
for (const item of list) {
const { objectId, object, data } = item;
// 들어온 payload 에서 object 가 없으면 data 로 대체
const payloadObject = object ?? data;
const rid = hashMap.get(objectId);
if (!rid) {
console.warn(`No rid found for objectId: ${objectId}`);
continue; // Skip this object if no rid is found
}
// 객체 형식 확인 및 문자열화
let objectString;
try {
objectString = typeof payloadObject === 'string'
? payloadObject
: JSON.stringify(payloadObject);
} catch (err) {
console.error('Error stringifying object:', err);
continue;
}
// DB 에 저장
await queryAsync(
'UPDATE class_session_objects SET data = ? WHERE rid = ?',
[objectString, rid]
);
// 브로드캐스트엔 object 프로퍼티로 통일
modified.push({
object: payloadObject,
objectId,
fromSocketId: socket.id // Add sender's socket ID to track source
});
}
// 다른 클라이언트에 전송 (자신에게는 전송하지 않음)
socket.broadcast
.to(roomId)
.emit('object-modified', {
roomId,
objects: modified.map(item => ({
object: item.object,
objectId: item.objectId
}))
});
} catch (err) {
console.error('Error processing object modification:', err);
socket.emit('error', {
message: 'Failed to process object modification',
error: err.message
});
}
});
socket.on('object-removed', async ({ roomId, objectId }) => {
try {
const startTime = Date.now(); // 삭제 시작 시간 기록
let rid = hashMap.get(objectId);
let deleteQuery;
let queryParams;
if (rid !== undefined) {
deleteQuery = `DELETE FROM class_session_objects WHERE rid = ?`;
queryParams = [rid];
} else {
deleteQuery = `DELETE FROM class_session_objects WHERE id = ?`;
queryParams = [objectId];
}
// 존재 여부 확인
const exists = await new Promise((resolve, reject) => {
const checkQuery = rid !== undefined
? `SELECT * FROM class_session_objects WHERE rid = ?`
: `SELECT * FROM class_session_objects WHERE id = ?`;
const checkParams = rid !== undefined ? [rid] : [objectId];
db.query(checkQuery, checkParams, (err, results) => {
if (err) {
reject(err);
} else {
resolve(results.length > 0);
}
});
});
if (!exists) {
console.log('Object not found for deletion');
return;
}
// 삭제 수행
await new Promise((resolve, reject) => {
db.query(deleteQuery, queryParams, (err, result) => {
if (err) {
console.error('Error executing delete query:', err);
reject(err);
} else {
resolve(result);
}
});
});
// 클라이언트에 삭제 완료 알림
socket.broadcast.to(roomId).emit('object-removed', { roomId, objectId });
const endTime = Date.now(); // 삭제 완료 시간 기록
console.log(`Object deletion took ${endTime - startTime} ms`);
} catch (err) {
console.error('Error in object-removed event:', err);
}
});
socket.on("image-lock", async (data) => {
socket.to(data.roomId).emit("image-lock", { roomId: data.roomId, disabled: data.disabled, userId: data.userId });
});
socket.on('imageLockChanged', ({ roomId, isImageLockActive, sourceUserId }) => {
socket.to(roomId).emit('imageLockChanged', { isImageLockActive, sourceUserId });
});
socket.on('changeBackground', async ({ roomId, bgType }) => {
try {
// 1) DB 업데이트
await new Promise((resolve, reject) => {
const sql = `UPDATE class_sessions SET bg = ? WHERE cs_id = ?`;
db.query(sql, [bgType, roomId], (err, result) => {
if (err) return reject(err);
resolve(result);
});
});
// 2) 업데이트 성공 시 broadcast
socket.broadcast.to(roomId).emit('changeBackground', { bgType });
} catch (error) {
console.error('[changeBackground] DB update error:', error);
// 여기서 필요한 경우 클라이언트에게 에러 전송도 가능
}
});
socket.on('user-viewport', (data) => {
try {
if (!data || !data.roomId) return;
// 데이터 유효성 검사
if (!data.viewportInfo ||
!data.viewportInfo.topLeft ||
!data.viewportInfo.bottomRight) {
return;
}
// 같은 방의 다른 사용자들에게 전달
socket.to(data.roomId).emit('user-viewport', {
userId: data.userId,
userName: data.userName,
viewportInfo: data.viewportInfo
});
} catch (error) {
console.error("Error handling user-viewport event:", error);
}
});
// Add this with other whiteboard socket event handlers
socket.on('update-viewport', (data) => {
try {
// Store only essential data to reduce memory usage and bandwidth
const essentialData = {
userId: data.userId,
realUserId: data.realUserId,
viewportTransform: data.viewportTransform,
canvasWidth: data.canvasWidth,
canvasHeight: data.canvasHeight,
zoom: data.zoom,
userName: data.userName,
role: data.role || 'student',
timestamp: data.timestamp || Date.now()
};
// Broadcast the viewport update to all other users in the room
socket.to(data.roomId).emit('user-viewport-update', essentialData);
} catch (error) {
console.error("Error handling viewport update:", error);
}
});
})
app.get("/get/curriculum/file", async (req, res) => {
const requestedFilePath = req.query.path; // Getting the file path from the query parameter
var custom_path = __dirname + "/curriculum/";
var filePath = path.join(custom_path, requestedFilePath);
try {
// Check if the file exists
await fs1.access(filePath, constants.F_OK);
// If file exists, send it as a response
res.sendFile(filePath); // This will send the file as a response
} catch (err) {
console.log(err);
res.status(404).send({ msg: "File not found." });
return;
}
});
app.get("/files/curriculum", async (req, res) => {
const directoryPath = path.join(__dirname, 'curriculum');
const recursiveSearch = async (dirPath, fileList = []) => {
const files = await fs1.readdir(dirPath);
for (const file of files) {
const filePath = path.join(dirPath, file);
const stat = await fs1.stat(filePath);
if (stat.isDirectory()) {
fileList = await recursiveSearch(filePath, fileList);
} else if (path.extname(file).toLowerCase() === '.pdf') {
fileList.push({
name: file,
path: filePath
});
}
}
return fileList;
}
try {
const pdfFiles = await recursiveSearch(directoryPath);
res.send(pdfFiles);
} catch (err) {
console.log('Error reading directory:', err.message);
res.status(500).send({ msg: "Error reading directory." });
}
});
app.post("/teacher/add/progress/report", (req, res) => {
// Assuming cs_id is passed within the request body to identify the class session
const cs_id = req.body.cs_id;
if (!cs_id) {
return res.status(400).send({ msg: "No cs_id provided." });
}
// Fetch the title and createdBy for the given cs_id
if (req.body.cs_id !== 'none') {
db.query('SELECT title, createdBy FROM class_sessions WHERE cs_id = ?', [cs_id], function (err, result) {
if (err) {
console.log('error', err.message, err.stack);
return res.status(500).send({ msg: "Error accessing the class_sessions table." });
}
if (!result.length) {
return res.status(404).send({ msg: "No class session found for the provided cs_id." });
}
const title = result[0].title;
const teacherName = result[0].createdBy;
// Insert the progress report into the ProgressReports table
const insertQuery = `
INSERT INTO ProgressReports (
weeks,
teacherName,
studentName,
studentUID,
assignmentCompleted,
academicTrend,
inClassUnderstanding,
behavior,
teacherComments,
submittedAt,
class_id,
class_name
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
const formattedDateTime = req.body.submittedAt.split('.')[0].replace('T', ' ');
const insertData = [
req.body.week,
teacherName,
req.body.studentName,
req.body.studentUID,
req.body.assignmentCompleted,
req.body.academicTrend,
req.body.inClassUnderstanding,
req.body.behavior,
req.body.teacherComments,
formattedDateTime, // use the formatted date-time string here
cs_id,
title
];
db.query(insertQuery, insertData, function (insertErr, insertResult) {
if (insertErr) {
console.log('insert error', insertErr.message, insertErr.stack);
return res.status(500).send({ msg: "Error inserting into the ProgressReports table." });
}
// Send success response
res.send({ msg: "Progress report successfully inserted.", reportId: insertResult.insertId });
});
});
} else {
const insertQuery = `
INSERT INTO ProgressReports (
weeks,
teacherName,
studentName,
studentUID,
assignmentCompleted,
academicTrend,
inClassUnderstanding,
behavior,
teacherComments,
submittedAt,
class_id,
class_name
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
const formattedDateTime = req.body.submittedAt.split('.')[0].replace('T', ' ');
const insertData = [
req.body.week,
req.body.teacherName,
req.body.studentName,
req.body.studentUID,
req.body.assignmentCompleted,
req.body.academicTrend,
req.body.inClassUnderstanding,
req.body.behavior,
req.body.teacherComments,
formattedDateTime, // use the formatted date-time string here
req.body.cs_id,
req.body.week,
];
db.query(insertQuery, insertData, function (insertErr, insertResult) {
if (insertErr) {
console.log('insert error', insertErr.message, insertErr.stack);
return res.status(500).send({ msg: "Error inserting into the ProgressReports table." });
}
// Send success response
res.send({ msg: "Progress report successfully inserted.", reportId: insertResult.insertId });
});
}
});
app.get("/teacher/get/progressreport", async (req, res) => {
const teacherId = req.query.teacherId; // Access the teacherId from query parameters
console.log("=1", teacherId);
// Decide the SQL query based on whether a teacherId is provided
let selectQuery;
if (teacherId) {
// Fetch only reports for the specified teacherId
selectQuery = `SELECT * FROM ProgressReports WHERE teacherName = ?`;
} else {
// Fetch all progress reports
selectQuery = `SELECT * FROM ProgressReports`;
}
try {
// Execute query with or without teacherId
const [reports] = await db.promise().query(selectQuery, teacherId ? [teacherId] : []);
const getTeacherInfo = async (id) => {
if (!id) return { teacherId: id, teacherName: "Unknown Teacher" };
try {
const [rows] = await db.promise().query('SELECT Fname, Lname FROM accounts WHERE id = ?', [id]);
if (rows.length) {
const { Fname, Lname } = rows[0];
return { teacherId: id, teacherName: `${Fname} ${Lname}` };
} else {
return { teacherId: id, teacherName: "No name found" };
}
} catch (err) {
console.error(err);
return { teacherId: id, teacherName: "Error fetching name" };
}
};
// Map through reports and enrich them with teacher information
const updatedReports = await Promise.all(reports.map(async report => {
// Use teacherName column which contains the teacher ID
const teacherInfo = await getTeacherInfo(report.teacherName);
return { ...report, teacherName: teacherInfo.teacherName };
}));
res.send(updatedReports);
} catch (err) {
console.error('Error executing query:', err.message);
res.status(500).send({ msg: "Error accessing the ProgressReports table." });
}
});
app.delete("/teacher/remove/progressreport/:reportId", (req, res) => {
const reportId = req.params.reportId;
// Check if reportId is provided
if (!reportId) {
return res.status(400).send({ msg: "No report ID provided." });
}
// Delete the progress report with the specified ID
const deleteQuery = `
DELETE FROM ProgressReports
WHERE id = ?
`;
db.query(deleteQuery, [reportId], function (err, result) {
if (err) {
console.log('delete error', err.message, err.stack);
return res.status(500).send({ msg: "Error deleting the progress report." });
}
// Check if any rows were affected (report with the provided ID exists)
if (result.affectedRows === 0) {
return res.status(404).send({ msg: "Progress report with the provided ID not found." });
}
// Send success response
res.send({ msg: "Progress report successfully deleted." });
});
});
app.get("/manager/get/alert", (req, res) => {
let response = {};
// Check schedule table
db.query('SELECT COUNT(*) as count FROM schedule WHERE notice IS NULL OR notice != true', function (err, result) {
if (err) {
console.log('error', err.message, err.stack);
return res.status(500).send({ msg: "Error accessing the schedule table." });
}
response.schedule = result[0].count;
// Check contact_list table
db.query('SELECT COUNT(*) as count FROM contact_list WHERE notice IS NULL OR notice != true', function (err, result) {
if (err) {
console.log('error', err.message, err.stack);
return res.status(500).send({ msg: "Error accessing the contact_list table." });
}
response.contact_list = result[0].count;
// Check message_reports table
db.query('SELECT COUNT(*) as count FROM message_reports WHERE notice IS NULL OR notice != true', function (err, result) {
if (err) {
console.log('error', err.message, err.stack);
return res.status(500).send({ msg: "Error accessing the message_reports table." });
}
response.message_reports = result[0].count;
// Send the final response
res.send(response);
});
});
});
});
app.delete('/canvas_sessions/:userId/:roomId', (req, res) => {
const { userId, roomId } = req.params;
let q1 = `DELETE FROM session_allowed_users WHERE roomId = ?`;
db.query(q1, [roomId], (err, result) => {
if (err) {
console.log(err);
return res.status(500).json({ error: 'An error occurred while deleting the session allowed users.' });
}
let q2 = `DELETE FROM canvas_sessions WHERE roomId = ?`;
db.query(q2, [roomId], (err, result) => {
if (err) {
console.log(err);
return res.status(500).json({ error: 'An error occurred while deleting the canvas session.' });
}
// Return success response
return res.json({ message: 'Canvas session deleted successfully.' });
});
});
});
app.post('/upload', async function (req, res) {
let sampleFile;
let uploadPath;
if (!req.files || Object.keys(req.files).length === 0) {
res.status(400).send('No files were uploaded.');
return;
}
sampleFile = req.files.file;
uploadPath = __dirname + '/uploads/' + sampleFile.name;
sampleFile.mv(uploadPath, function (err) {
if (err) {
return res.status(500).send(err);
}
res.send('File uploaded to ' + uploadPath);
});
});
app.get('/uploads/get/:file', function (req, res, next) {
var filenamewithpath = __dirname + '/uploads/' + req.params.file;
if (!fs1.existsSync(filenamewithpath)) {
res.status(404).json({ 'message': 'file not found' })
return;
}
res.download(filenamewithpath)
});
app.post("/user/remove/by/id", (req, res) => {
// Update the status to 'Deactivated' instead of deleting the record
var sql = 'UPDATE accounts SET status = "Deactivated" WHERE id = ' + mysql.escape(req.body.uid);
db.query(sql, function (err, result) {
if (err) {
console.log('error', err.message, err.stack);
res.send({ msg: "Error in updating the status." }); // Send error message
} else if (result.affectedRows > 0) {
res.send({ msg: "User status has been updated to Deactivated." }); // Send success message
} else {
res.send({ msg: "No user found with the provided ID." }); // Send message if no user is found
}
});
});
app.post("/user/add/info/manually", (req, res) => { //Add by manager
var uid = getRandomInt();
var sql = 'INSERT INTO accounts (child_list, id, Fname, Lname, Role, status, Pnumber, Address, Zip, email, numofchild, invoice, age, birthday, gender, school, grade, expected_grad, emergency_1_n, emergency_1_p, emergency_2_n, emergency_2_p, note, password, makeUp_credit, Cancellation_Credit) VALUES ?';
var val = [
[req.body.child_list, uid, req.body.firstName, req.body.lastName, req.body.role, req.body.status, req.body.phoneNumber, req.body.address, req.body.zip, req.body.email, req.body.noOfChild, '0', req.body.age, req.body.birthday, req.body.gender, req.body.school, req.body.grade, req.body.expectedGraduate, req.body.emergencyContactName1, req.body.emergencyContactNo1, req.body.emergencyContactName2, req.body.emergencyContactNo2, req.body.notes, "mmt1234!", req.body.Makeup_Credit, req.body.cancle_credit]
];
db.query(sql, [val], function (err, result) {
if (err)
console.log('error', err.message, err.stack)
if (result) {
res.send({
id: uid,
name: req.body.firstName + ", " + req.body.lastName,
email: req.body.email,
status: req.body.status,
role: req.body.role,
invoice: 0
});
}
});
});
app.post("/user/add/info", (req, res) => {
var sql = 'INSERT INTO accounts (id, Fname, Lname, Role, status, Pnumber, Address, Zip, email, password, makeUp_credit) VALUES ?';
var val = [
[getRandomInt(), req.body.fname, req.body.lname, req.body.role, "Registered", req.body.pnum, req.body.address, req.body.zip, req.body.email, req.body.pswd, 0]
];
db.query(sql, [val], function (err, result) {
if (result) {
res.send({ success: "Successfully register to the website." });
}
if (err) {
res.send({ message: "Sorry we do have the duplicate email." });
}
});
});
app.post("/user/logout", (req, res) => {
req.session.login = "";
req.session.role = "";
req.session.uid = "";
req.session.destroy();
res.send({
message: "Logout"
});
});
app.post("/mmtlogin", (req, res) => {
console.log(req.body);
//var sql = 'SELECT * FROM accounts WHERE email = ' + mysql.escape(req.body.uid);
});
app.post("/user/login", (req, res) => {
console.log(req.body);
var sql = 'SELECT * FROM accounts WHERE email = ' + mysql.escape(req.body.uid);
db.query(sql, function (err, result) {
if (err) {
res.send({ message: "An error occurred." });
} else if (result[0]) {
console.log("=result[0]=", result[0]);
if (result[0].status === 'deactivate') {
res.send({ message: "This account has been blocked." });
} else if (result[0].password == req.body.password) {
req.session.login = true;
req.session.uid = result[0].id;
req.session.role = result[0].Role;
req.session.save()
res.send({
message: "Login",
login: "True",
role: result[0].Role,
...result[0]
});
} else {
res.send({ message: "Please check the email or password again." });
}
} else {
res.send({ message: "Please check the email or password again." });
}
});
});
app.get("/user/get/info/member/all", (req, res) => {
var sql = 'SELECT * FROM accounts';
var name = [];
var id = [];
var email = [];
var status = [];
var Role = [];
var invoice = [];
var makeUp_credit = [];
db.query(sql, function (err, result) {
for (let i = 0; i < result.length; i++) {
id.push(result[i].id);
name.push(result[i].Fname + ", " + result[i].Lname);
email.push(result[i].email);
status.push(result[i].status);
Role.push(result[i].Role);
invoice.push(result[i].invoice);
makeUp_credit.push(result[i].makeUp_credit);
}
res.send({
id: id,
name: name,
email: email,
status: status,
Role: Role,
invoice: invoice,
makeUp_credit: makeUp_credit
});
});
});
app.post("/user/update/one/info", (req, res) => {
let sql = `UPDATE accounts SET id=` + mysql.escape(req.body.id)
+ `, Fname=` + mysql.escape(req.body.Fname)
+ `, Lname=` + mysql.escape(req.body.Lname)
+ `, Pnumber=` + mysql.escape(req.body.Pnumber)
+ `, Address=` + mysql.escape(req.body.Address)
+ `, Zip=` + mysql.escape(req.body.Zip)
+ `, age=` + mysql.escape(req.body.age)
+ ` WHERE id=` + mysql.escape(req.body.id)
db.query(sql, function (err, result) {
if (err)
console.log('error', err.message, err.stack)
if (result) {
res.send({
});
}
});
});
app.get("/user/get/one/info", (req, res) => {
var sql = 'SELECT * FROM accounts WHERE id = ' + mysql.escape(req.session.uid);
db.query(sql, function (err, result) {
if (err)
console.log('error', err.message, err.stack)
if (result) {
res.send({
info: result[0]
});
}
});
});
app.post("/user/get/info/member/id", (req, res) => {
var sql = 'SELECT * FROM accounts WHERE id = ' + mysql.escape(req.body.uid);
db.query(sql, function (err, result) {
if (err)
console.log('error', err.message, err.stack)
if (result) {
res.send({
info: result[0]
});
}
});
});
app.post("/user/update/info/member/id", (req, res) => {
var sql = `UPDATE accounts SET `
+ `child_list=` + mysql.escape(req.body.child_list)
+ `, Fname=` + mysql.escape(req.body.firstName)
+ `, Lname=` + mysql.escape(req.body.lastName)
+ `, Role=` + mysql.escape(req.body.role)
+ `, status=` + mysql.escape(req.body.status)
+ `, Pnumber=` + mysql.escape(req.body.phoneNumber)
+ `, Address=` + mysql.escape(req.body.address)
+ `, Zip=` + mysql.escape(req.body.zip)
+ `, email=` + mysql.escape(req.body.email)
+ `, numofchild=` + mysql.escape(req.body.noOfChild)
+ `, age=` + mysql.escape(req.body.age)
+ `, birthday=` + mysql.escape(req.body.birthday)
+ `, gender=` + mysql.escape(req.body.gender)
+ `, school=` + mysql.escape(req.body.school)
+ `, grade=` + mysql.escape(req.body.grade)
+ `, expected_grad=` + mysql.escape(req.body.expectedGraduate)
+ `, emergency_1_n=` + mysql.escape(req.body.emergencyContactName1)
+ `, emergency_1_p=` + mysql.escape(req.body.emergencyContactNo1)
+ `, emergency_2_n=` + mysql.escape(req.body.emergencyContactName2)
+ `, emergency_2_p=` + mysql.escape(req.body.emergencyContactNo2)
+ `, note=` + mysql.escape(req.body.notes)
+ `, makeUp_credit=` + mysql.escape(req.body.Makeup_Credit)
+ `, Cancellation_Credit=` + mysql.escape(req.body.cancle_credit)
+ ` WHERE id =` + mysql.escape(req.body.uid);
db.query(sql, function (err, result) {
if (err)
console.log('error', err.message, err.stack)
if (result) {
res.send({});
}
});
});
app.get("/user/get/login", (req, res) => {
if (req.session.login) {
res.send({
message: "True",
role: req.session.role
})
} else {
res.send({ message: "False" })
}
});
// 채팅을 위한 사용자 목록 가져오기
// 채팅을 위한 사용자 목록 가져오기
app.get('/user/get/for/chat', async (req, res) => {
try {
const currentUserId = req.query.currentUserId;
if (!currentUserId) {
return res.status(400).json({ message: 'Current user ID is required' });
}
// 필요한 정보만 선택적으로 가져오기 (id, Fname, Lname, Role)
const query = `
SELECT id, Fname, Lname, Role
FROM accounts
WHERE id != ?
AND (Role = 'student' OR Role = 'Teacher' OR Role = 'parents' OR Role = 'admin' OR Role = 'manager')
AND status = 'active'
ORDER BY
CASE
WHEN Role = 'Teacher' THEN 1
WHEN Role = 'student' THEN 2
WHEN Role = 'parents' THEN 3
WHEN Role = 'admin' THEN 4
WHEN Role = 'manager' THEN 5
ELSE 6
END,
Fname
`;
db.query(query, [currentUserId], (err, results) => {
if (err) {
console.error('Error fetching chat users:', err);
return res.status(500).json({ message: 'Internal server error' });
}
res.status(200).json({ users: results });
});
} catch (error) {
console.error('Error in get users for chat:', error);
res.status(500).json({ message: 'Internal server error' });
}
});
app.get("/conversations/:id/is_call_active", (req, res) => {
const cs_id = req.params.id;
const call = calls.find((call) => call.conversationId === cs_id);
res.send({
isCallActive: call != undefined,
callData: call
});
});
app.get("/conversations/:id/join_conversation", (req, res) => {
const cs_id = req.params.id;
const socketId = req.query.socketId;
const call = calls.find((call) => call.conversationId === cs_id);
// const socketIdToCSIdMap = {};
// const usersInConversation = {};
// Get previous conversationId of socket
const prevCSId = socketIdToCSIdMap[socketId];
// If this entry did not exist, this is a new socket, so no cleanup is required
if (prevCSId) {
const usersForPrevConversation = usersInConversation[prevCSId] || [];
usersInConversation[cs_id] = usersForPrevConversation.filter(x => x != socketId);
}
socketIdToCSIdMap[socketId] = cs_id;
const usersForThisConversation = usersInConversation[cs_id] || [];
usersInConversation[cs_id] = usersForThisConversation.push(socketId);
res.send({
call: call
});
});
app.post("/user/get/info/addressNphoneNname", (req, res) => {
var sql = 'SELECT * FROM accounts WHERE id = ' + mysql.escape(req.body.id);
var pnum = [];
var email = [];
var name = [];
var uid = [];
var address = [];
db.query(sql, function (err, result) {
for (let i = 0; i < result.length; i++) {
pnum.push(result[i].Pnumber);
email.push(result[i].email);
name.push(result[i].Fname + ", " + result[i].Lname);
address.push(result[i].address);
uid.push(result[i].id);
}
res.send({
pnum: pnum,
email: email,
name: name,
address: address,
uid: uid
});
});
});
app.post("/user/get/info/parents/namenid", (req, res) => {
var sql = 'SELECT id FROM accounts WHERE Role = ' + mysql.escape("parents");
var data = [];
db.query(sql, function (err, result) {
if (result) {
for (let i = 0; i < result.length; i++) {
var sql2 = 'SELECT * FROM accounts WHERE id = ' + mysql.escape(result[i].id);
db.query(sql2, function (err, result2) {
var cname = result2[0].Fname + ", " + result2[0].Lname;
var cid = result[i].id;
data.push({ cname, cid });
});
}
db.query("", function (err, result) {
res.send({
data: data
});
});
}
});
});
app.post("/class/add", (req, res) => {
var sql = 'INSERT INTO class_lst (id, name, description, price) VALUES ?';
var val = [
[getRandomInt3(), req.body.title, req.body.describe, req.body.price]
];
db.query(sql, [val], function (err, result) {
if (err)
console.log('error', err.message, err.stack)
if (result) {
res.send({
message: "Add class to the list successfully."
});
}
});
});
app.post("/class/update/one", (req, res) => {
const cid = req.body.cid;
const ctitle = req.body.ctitle;
const cprice = req.body.cprice;
const c_des = req.body.c_des;
var sql = `UPDATE class_lst SET name = ${mysql.escape(ctitle)}, price = ${mysql.escape(cprice)}, description = ${mysql.escape(c_des)} WHERE id = ${mysql.escape(cid)}`;
db.query(sql, function (err, result) {
if (err) {
console.error(err);
res.status(500).send("Error updating class");
} else {
res.send({ message: "Class updated successfully" });
}
});
});
app.post("/class/del/one", (req, res) => {
var sql = 'DELETE FROM class_lst WHERE id = ' + mysql.escape(req.body.cid);
db.query(sql, function (err, result) {
if (err) {
console.error(err);
res.status(500).send("Error deleting class");
} else {
res.send({ message: "Class deleted successfully" });
}
});
});
app.post("/class/get/one", (req, res) => {
var sql = 'SELECT * FROM class_lst WHERE id = ' + mysql.escape(req.body.cid);
var cid = [];
var title = [];
var price = [];
var description = [];
db.query(sql, function (err, result) {
for (let i = 0; i < result.length; i++) {
cid.push(result[i].id);
title.push(result[i].name);
price.push(result[i].price);
description.push(result[i].description);
}
res.send({
c_id: cid,
title: title,
price: price,
description: description
});
});
});
app.post("/class/get/all", (req, res) => {
var sql = 'SELECT * FROM class_lst';
var title = [];
var price = [];
var description = [];
var cid = [];
db.query(sql, function (err, result) {
for (let i = 0; i < result.length; i++) {
title.push(result[i].name);
price.push(result[i].price);
description.push(result[i].description);
cid.push(result[i].id);
}
res.send({
title: title,
price: price,
description: description,
cid: cid
});
});
});
app.post("/user/login/google", async (req, res, next) => {
const { email } = req.body;
let sql = `SELECT * FROM accounts WHERE email = '${email}'`;
db.query(sql, function (err, result) {
if (err) {
console.log(err);
res.send({ message: "An error occurred." });
} else if (result[0]) {
if (result[0].status === 'deactivate') {
res.send({ message: "This account has been blocked." });
} else {
req.session.login = true;
req.session.role = result[0].Role;
req.session.uid = result[0].id;
res.send({
'id': result[0].id,
'email': result[0].email,
'first_name': result[0].first_name,
'last_name': result[0].last_name,
'Role': result[0].Role,
// 필요한 다른 사용자 정보
'message': "Login"
});
}
} else {
res.send({
'register': "need to add the info to the server",
'gmail': req.body.email
});
}
});
});
app.get("/files/uploads/blogpost/:id/:img", async (req, res, next) => {
var custom_path = __dirname + "/routes/uploads/blogpost/" + req.params.id;
var filePath = path.join(custom_path, req.params.img);
res.download(filePath);
});
app.get("/files/uploads/profile/:file", async (req, res, next) => {
var custom_path = __dirname + "/routes/uploads/profile/";
var filePath = path.join(custom_path, req.params.file);
res.download(filePath);
});
app.get("/files/uploads/testmonial/:file", async (req, res, next) => {
var custom_path = __dirname + "/routes/uploads/testmonial/";
var filePath = path.join(custom_path, req.params.file);
res.download(filePath);
});
app.post("/search/parent/by/name", async (req, res, next) => {
var search = req.body.parent_search;
let sql = "SELECT * FROM accounts WHERE Fname LIKE '%" + search + "%'";
db.query(sql, function (err, result) {
var uid = [];
var Fname = [];
var Lname = [];
var role = [];
for (let i = 0; i < result.length; i++) {
if (result[i].Role == "parents") {
uid.push(result[i].id);
Fname.push(result[i].Fname);
Lname.push(result[i].Lname);
role.push(result[i].Role);
}
}
res.send({
'uid': uid,
'Fname': Fname,
'Lname': Lname,
'role': role
})
});
});
app.post("/search/child/by/name", async (req, res, next) => {
var search = req.body.child_search;
var searchTerms = search.split(' ');
var firstName = searchTerms[0];
var lastName = searchTerms.length > 1 ? searchTerms[1] : '';
let sql = "SELECT * FROM accounts WHERE Fname LIKE ? AND Lname LIKE ?";
db.query(sql, [`%${firstName}%`, `%${lastName}%`], function (err, result) {
var uid = [];
var Fname = [];
var Lname = [];
var role = [];
for (let i = 0; i < result?.length; i++) {
uid.push(result[i].id);
Fname.push(result[i].Fname);
Lname.push(result[i].Lname);
role.push(result[i].Role);
}
res.send({
'uid': uid,
'Fname': Fname,
'Lname': Lname,
'role': role
})
});
});
app.get('/user/get/invoice/by/id', async (req, res, next) => {
var sql = 'SELECT * FROM invoice WHERE c_uid = ' + mysql.escape(req.session.uid);
db.query(sql, function (err, result) {
if (err)
console.log('error', err.message, err.stack)
if (result) {
res.send({
data: result
});
}
});
});
app.get('/get/images', async (req, res) => {
const imagesDir = path.join(__dirname, 'images');
const imageExtensions = ['.jpg', '.png'];
fs1.readdir(imagesDir, (err, files) => {
if (err) {
return res.status(500).send({ error: 'Error reading images directory' });
}
const images = files.filter(file => imageExtensions.includes(path.extname(file)));
const imagesPaths = [];
for (var i = 0; i < images.length; i++) {
imagesPaths.push(`${basic + "/images/" + images[i]}`)
}
res.send({ imagesPaths });
});
});
app.get("/images/:img", async (req, res, next) => {
var custom_path = __dirname + "/images/";
var filePath = path.join(custom_path, req.params.img);
res.download(filePath);
});
app.post("/manager/update/user/password", async (req, res, next) => {
var id = req.body.account_id;
var psd = req.body.password;
var sql = "UPDATE accounts SET password = '" + psd + "' WHERE id = '" + id + "'";
db.query(sql, function (err, result) {
res.send({
msg: "UPDATE the password"
})
});
});
app.get('/get/jobRoles', async (req, res) => {
try {
db.query('SELECT * FROM JobRoles', function (err, result) {
if (err) {
console.error('Error querying the database:', err);
return res.status(500).send('Database query error');
}
res.json(result);
});
} catch (err) {
res.status(500).send({
error: 'Database error while retrieving job roles',
details: err.message
});
}
});
app.post('/post/jobRoles', async (req, res) => {
const { role_id, role, open_date, description, qualification, requirement } = req.body;
// Decide if it's an update or insert based on the presence of `role_id`
if (role_id) {
// Update existing job role
const updateSql = `
UPDATE JobRoles
SET role = ?, open_date = ?, description = ?, qualification = ?, requirement = ?
WHERE role_id = ?;
`;
const updateValues = [role, open_date, description, qualification, requirement, role_id];
db.query(updateSql, updateValues, function (err, result) {
if (err) {
console.error('Error updating job role:', err);
return res.status(500).send({ error: 'Database operation failed', details: err.message });
}
res.send({ message: 'Job role updated successfully', role_id: role_id });
});
} else {
// Insert new job role
const newRoleId = `MMT_${Math.floor(Math.random() * 100000)}`;
const formattedDate = moment(open_date).format('YYYY-MM-DD'); // Ensure the format matches your DB expectation
const insertSql = `
INSERT INTO JobRoles (role_id, role, open_date, description, qualification, requirement)
VALUES (?, ?, ?, ?, ?, ?);
`;
const insertValues = [newRoleId, role, formattedDate, description, qualification, requirement];
db.query(insertSql, insertValues, function (err, result) {
if (err) {
console.error('Error inserting new job role:', err);
return res.status(500).send({ error: 'Database operation failed', details: err.message });
}
res.send({ message: 'Job role created successfully', role_id: newRoleId });
});
}
});
app.delete('/delete/jobRoles/:role_id', async (req, res) => {
const { role_id } = req.params;
if (!role_id) {
return res.status(400).send({ error: 'Missing role_id for deletion' });
}
try {
const sql = 'DELETE FROM JobRoles WHERE role_id = ?';
const values = [role_id];
db.query(sql, values, function (err, result) {
if (err) {
console.error('Error executing query', err.stack);
return res.status(500).send({ error: 'Database operation failed', details: err.message });
}
console.log(result)
if (result.affectedRows > 0) {
res.send({ message: 'Job role deleted successfully' });
} else {
res.status(404).send({ message: 'Job role not found' });
}
});
} catch (err) {
res.status(500).send({ error: 'Deletion failed', details: err.message });
}
});
app.post('/insert/pathway', async (req, res) => {
try {
console.log(req.body);
const { ID, name, created_time, created_by, modified } = req.body;
// Prepare the SQL query using parameterized queries to prevent SQL injection
const sql = `
INSERT INTO modules (ID, name, created_time, created_by, modified)
VALUES (?, ?, ?, ?, ?)
`;
const values = [ID, name, created_time, created_by, modified];
db.query(sql, values, (err, result) => {
if (err) {
console.error('Error inserting into the database:', err);
return res.status(500).json({ error: 'Database insert error', details: err.message });
}
res.status(200).json({ message: 'Module inserted successfully', moduleId: ID });
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({
error: 'Unexpected error while inserting module',
details: err.message,
});
}
});
app.get('/get/pathway', async (req, res) => {
try {
const sql = 'SELECT * FROM modules';
db.query(sql, (err, results) => {
if (err) {
console.error('Error querying the database:', err);
return res.status(500).json({
error: 'Database query error',
details: err.message,
});
}
// Optionally parse the 'modified' field from JSON string to object
const modules = results.map((module) => {
return {
...module,
modified: JSON.parse(module.modified),
};
});
res.status(200).json(modules);
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({
error: 'Unexpected error while retrieving modules',
details: err.message,
});
}
});
app.get('/user/credit', async (req, res) => {
const { user_id } = req.query; // Get the ticket ID from the route parameters
console.log(user_id);
try {
const sql = `SELECT makeUp_credit from accounts WHERE id = ?`;
db.query(sql, [user_id], (err, result) => {
if (err) {
console.error('Error updating ticket status:', err);
return res.status(500).json({ error: 'Database update error', details: err.message });
}
res.status(200).json(result[0].makeUp_credit);
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({ error: 'Unexpected error while updating ticket status', details: err.message });
}
});
app.put('/user/credit', async (req, res) => {
const { user_id, credit } = req.body; // Get user ID and new credit value from the request body
console.log(user_id, credit);
// Check if both user_id and credit are provided
if (!user_id || !credit) {
return res.status(400).json({ error: 'Missing required fields: user_id or credit' });
}
// Ensure credit is a number and not negative
const creditValue = Number(credit);
if (isNaN(creditValue) || creditValue < 0) {
return res.status(400).json({ error: 'Invalid credit value. Must be a non-negative number.' });
}
try {
const sql = `UPDATE accounts SET makeUp_credit = ? WHERE id = ?`;
// Execute the SQL query to update the user's credit
db.query(sql, [creditValue, user_id], (err, result) => {
if (err) {
console.error('Error updating user credit:', err);
return res.status(500).json({ error: 'Database update error', details: err.message });
}
// Check if the user was found and updated
if (result.affectedRows === 0) {
return res.status(404).json({ message: `User with ID: ${user_id} not found` });
}
// Successfully updated the user's credit
res.status(200).json({ message: 'User credit updated successfully' });
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({ error: 'Unexpected error while updating user credit', details: err.message });
}
});
app.put('/ticket/status/:id', async (req, res) => {
const { id } = req.params; // Get the ticket ID from the route parameters
const { status } = req.body; // Get the status from the request body
if (!status) {
return res.status(400).json({ error: 'Missing required field: status' });
}
try {
const sql = `UPDATE ticket SET status = ? WHERE id = ?`;
db.query(sql, [status, id], (err, result) => {
if (err) {
console.error('Error updating ticket status:', err);
return res.status(500).json({ error: 'Database update error', details: err.message });
}
if (result.affectedRows === 0) {
return res.status(404).json({ message: `Ticket with ID: ${id} not found` });
}
res.status(200).json({ message: 'Ticket status updated successfully' });
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({ error: 'Unexpected error while updating ticket status', details: err.message });
}
});
app.put('/ticket/context/:id', async (req, res) => {
const { id } = req.params; // Get the ticket ID from the URL
const { context, status } = req.body; // Get the updated context from the request body
if (!context) {
return res.status(400).json({ error: 'Missing required field: context' });
}
try {
const sql = `UPDATE ticket SET context = ?, status = ? WHERE id = ?`;
db.query(sql, [context, status, id], (err, result) => {
if (err) {
console.error('Error updating ticket context:', err);
return res.status(500).json({ error: 'Database update error', details: err.message });
}
if (result.affectedRows === 0) {
return res.status(404).json({ message: `Ticket with ID: ${id} not found` });
}
res.status(200).json({ message: 'Ticket context updated successfully' });
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({ error: 'Unexpected error while updating ticket context', details: err.message });
}
});
app.get('/ticket', async (req, res) => {
const { user_id, role } = req.query;
console.log('user_id:', user_id);
console.log('role:', role);
if (!user_id) {
return res.status(400).json({
error: 'Missing required field: user_id',
});
}
try {
// If role is 'admin', fetch all tickets. Otherwise, fetch tickets for a specific user.
const sql = role === "admin"
? 'SELECT * FROM ticket'
: 'SELECT * FROM ticket WHERE user_id = ?';
const queryParams = role === "admin" ? [] : [user_id];
// Execute the query
db.query(sql, queryParams, (err, results) => {
if (err) {
console.error('Error querying the database:', err);
return res.status(500).json({
error: 'Database query error',
details: err.message,
});
}
// Return the tickets
res.status(200).json(results);
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({
error: 'Unexpected error while retrieving tickets',
details: err.message,
});
}
});
app.post('/ticket', async (req, res) => {
const { status, title, level, context, created_date, userid, user_name } = req.body;
if (!status || !title || !level || !context || !created_date) {
return res.status(400).json({
error: 'Missing required fields: status, title, level, context, or created_date',
});
}
try {
// SQL query to insert the new ticket
const sql = `
INSERT INTO ticket (status, title, level, context, created_date, user_id, user_name)
VALUES (?, ?, ?, ?, ?, ?, ?)
`;
// Execute the query with provided data
db.query(sql, [status, title, level, context, created_date, userid, user_name], (err, result) => {
if (err) {
console.error('Error inserting ticket into database:', err);
return res.status(500).json({
error: 'Database insertion error',
details: err.message,
});
}
res.status(201).json({
message: 'Ticket created successfully',
ticketId: result.insertId, // Optionally return the new ticket ID
});
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({
error: 'Unexpected error while creating ticket',
details: err.message,
});
}
});
app.delete('/del/pathway/:id', async (req, res) => {
try {
const moduleId = req.params.id;
// Input validation
if (!moduleId) {
return res.status(400).json({ error: 'Module ID is required' });
}
// Prepare the SQL query using parameterized queries to prevent SQL injection
const sql = 'DELETE FROM modules WHERE ID = ?';
const values = [moduleId];
db.query(sql, values, (err, result) => {
if (err) {
console.error('Error deleting module from the database:', err);
return res.status(500).json({
error: 'Database delete error',
details: err.message,
});
}
if (result.affectedRows === 0) {
// No module found with the given ID
return res.status(404).json({ error: 'Module not found' });
}
res.status(200).json({ message: 'Module deleted successfully' });
});
} catch (err) {
console.error('Unexpected error:', err);
res.status(500).json({
error: 'Unexpected error while deleting module',
details: err.message,
});
}
});
//=================================
app.get('/api/data/:uuid/data.json', (req, res) => {
const dataDir = path.join(__dirname, 'chrome_extension');
const userDir = path.join(dataDir, req.params.uuid);
const dataFile = path.join(userDir, 'data.json');
if (!fs1.existsSync(dataFile)) {
return res.json([]);
}
const content = fs1.readFileSync(dataFile, 'utf8');
const arr = JSON.parse(content);
res.json(arr);
});
app.post('/api/data/:uuid', (req, res) => {
const dataDir = path.join(__dirname, 'chrome_extension');
const userDir = path.join(dataDir, req.params.uuid);
if (!fs1.existsSync(userDir)) fs1.mkdirSync(userDir, { recursive: true });
const dataFile = path.join(userDir, 'data.json');
let arr = [];
if (fs1.existsSync(dataFile)) {
arr = JSON.parse(fs1.readFileSync(dataFile, 'utf8'));
}
arr.push(req.body);
fs1.writeFileSync(dataFile, JSON.stringify(arr, null, 2), 'utf8');
res.json({ status: "ok" });
});
app.delete('/api/data/:uuid/:id', (req, res) => {
const dataDir = path.join(__dirname, 'chrome_extension');
const userDir = path.join(dataDir, req.params.uuid);
const dataFile = path.join(userDir, 'data.json');
if (!fs1.existsSync(dataFile)) {
return res.status(404).json({ error: "Not found" });
}
let arr = JSON.parse(fs1.readFileSync(dataFile, 'utf8'));
const index = arr.findIndex(x => x.id === req.params.id);
if (index === -1) return res.status(404).json({ error: "Not found" });
arr.splice(index, 1);
fs1.writeFileSync(dataFile, JSON.stringify(arr, null, 2), 'utf8');
res.json({ status: "ok" });
});
app.patch('/api/data/:uuid/:id', (req, res) => {
const dataDir = path.join(__dirname, 'chrome_extension');
const userDir = path.join(dataDir, req.params.uuid);
const dataFile = path.join(userDir, 'data.json');
if (!fs1.existsSync(dataFile)) {
return res.status(404).json({ error: "Not found" });
}
let arr = JSON.parse(fs1.readFileSync(dataFile, 'utf8'));
const index = arr.findIndex(x => x.id === req.params.id);
if (index === -1) return res.status(404).json({ error: "Not found" });
if (req.body.title) {
arr[index].title = req.body.title;
}
fs1.writeFileSync(dataFile, JSON.stringify(arr, null, 2), 'utf8');
res.json({ status: "ok" });
});
const groq = new Groq({ apiKey: "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" });
const availableModels = {
// Production Models
"llama": "llama-3.3-70b-versatile",
"llama-light": "llama-3.1-8b-instant",
"llama-guard": "llama-guard-3-8b",
"llama3-70b": "llama3-70b-8192",
"llama3-8b": "llama3-8b-8192",
"mixtral": "mixtral-8x7b-32768",
"gemma2": "gemma2-9b-it",
"whisper-large": "whisper-large-v3",
"whisper-turbo": "whisper-large-v3-turbo",
"distil-whisper": "distil-whisper-large-v3-en",
// Preview Models
"qwen-qwq": "qwen-qwq-32b",
"mistral-saba": "mistral-saba-24b",
"qwen-coder": "qwen-2.5-coder-32b",
"qwen-2.5": "qwen-2.5-32b",
"deepseek-qwen": "deepseek-r1-distill-qwen-32b",
"deepseek-specdec": "deepseek-r1-distill-llama-70b-specdec",
"deepseek": "deepseek-r1-distill-llama-70b",
"llama-specdec": "llama-3.3-70b-specdec",
"llama-1b": "llama-3.2-1b-preview",
"llama-3b": "llama-3.2-3b-preview",
"llama-vision-11b": "llama-3.2-11b-vision-preview",
"llama-vision-90b": "llama-3.2-90b-vision-preview"
};
async function getGroqChatCompletion(prompt, chatHistory = [], options = {}) {
const { model = 'llama-3.3-70b-versatile', max_tokens = 8000, temperature = 0.7, top_p = 0.95, frequency_penalty = 0, presence_penalty = 0 } = options;
try {
const historyMessage = chatHistory.length ? { role: 'system', content: `Conversation History:\n${chatHistory.join("\n")}` } : null;
const messages = [
{ role: 'system', content: 'You are a friendly, supportive teacher who guides students like a caring friend. Provide step-by-step help while encouraging them to find the answer on their own.' },
...(historyMessage ? [historyMessage] : []),
{ role: 'user', content: prompt }
];
const response = await groq.chat.completions.create({
messages,
model,
max_tokens,
temperature,
top_p,
frequency_penalty,
presence_penalty,
});
return response.choices[0]?.message?.content || 'No response from Groq';
} catch (err) {
console.error('Error calling Groq:', err.message);
throw err;
}
}
app.post('/askai/:modelId', (req, res) => {
const modelId = req.params.modelId;
let modelName = availableModels["llama"];
if (modelId && availableModels[modelId]) {
modelName = availableModels[modelId];
}
// Extract parameters from request body
const { prompt, chatHistory = [], room_id, user_id, user_name } = req.body;
// Validate required parameters
if (!prompt || !user_id || !user_name) {
return res.status(400).json({
error: 'Required parameters missing',
details: 'prompt, user_id, and user_name are required'
});
}
console.log(`Using model: ${modelName} for prompt from user: ${user_name}`);
console.log(`Room ID: ${room_id || 'Not provided (will create new)'}`);
// Call Groq API for AI response
getGroqChatCompletion(prompt, chatHistory, {
model: modelName,
max_tokens: req.body.max_tokens || 8000,
temperature: req.body.temperature || 0.7,
top_p: req.body.top_p || 0.95,
frequency_penalty: req.body.frequency_penalty || 0,
presence_penalty: req.body.presence_penalty || 0,
}).then(answer => {
const cleanedAnswer = answer.replace(/<think>[\s\S]*?<\/think>/g, '').trim();
// Function to insert messages into DB
function insertMessages(roomId) {
console.log(`Inserting ai_chat_messages for room ${roomId}`);
// Insert user message
const sqlInsertUserMsg = `INSERT INTO ai_chat_messages (room_id, user_id, user_name, model_id, role, message)
VALUES (?, ?, ?, ?, 'user', ?)`;
db.query(sqlInsertUserMsg, [roomId, user_id, user_name, modelId, prompt], (err, userMsgResult) => {
if (err) {
console.error('Error inserting user message:', err);
return res.status(500).json({ error: 'Database query error1', details: err.message });
}
// Insert assistant message
const sqlInsertAssistantMsg = `INSERT INTO ai_chat_messages (room_id, user_id, user_name, model_id, role, message)
VALUES (?, ?, ?, ?, 'assistant', ?)`;
db.query(sqlInsertAssistantMsg, [roomId, user_id, user_name, modelId, cleanedAnswer], (err, assistantMsgResult) => {
if (err) {
console.error('Error inserting assistant message:', err);
return res.status(500).json({ error: 'Database query error2', details: err.message });
}
// Return successful response
return res.status(200).json({
answer: cleanedAnswer,
model: { id: modelId, name: modelName },
room_id: roomId
});
});
});
}
// If room_id is not provided, create a new chat room
if (!room_id) {
console.log(`Creating new room for user ${user_id} (${user_name})`);
const newRoomName = prompt.length > 30 ? prompt.substring(0, 30) + '...' : prompt;
const sqlInsertRoom = `INSERT INTO ai_chat_rooms (user_id, user_name, room_name) VALUES (?, ?, ?)`;
db.query(sqlInsertRoom, [user_id, user_name, newRoomName], (err, roomResult) => {
if (err) {
console.error('Error creating new room:', err);
return res.status(500).json({ error: 'Database query error', details: err.message });
}
const newRoomId = roomResult.insertId;
console.log(`Created new room with ID: ${newRoomId}`);
insertMessages(newRoomId);
});
} else {
// Check if the room exists first
const sqlCheckRoom = `SELECT id FROM ai_chat_rooms WHERE id = ?`;
db.query(sqlCheckRoom, [room_id], (err, roomResult) => {
if (err) {
console.error('Error checking room existence:', err);
return res.status(500).json({ error: 'Database query error', details: err.message });
}
if (roomResult.length === 0) {
console.log(`Room ${room_id} not found. Creating a new room.`);
// Room doesn't exist, create a new one
const newRoomName = prompt.length > 30 ? prompt.substring(0, 30) + '...' : prompt;
const sqlInsertRoom = `INSERT INTO ai_chat_rooms (user_id, user_name, room_name) VALUES (?, ?, ?)`;
db.query(sqlInsertRoom, [user_id, user_name, newRoomName], (err, newRoomResult) => {
if (err) {
console.error('Error creating new room:', err);
return res.status(500).json({ error: 'Database query error', details: err.message });
}
const newRoomId = newRoomResult.insertId;
console.log(`Created new room with ID: ${newRoomId}`);
insertMessages(newRoomId);
});
} else {
// Room exists, proceed with inserting messages
console.log(`Room ${room_id} found, proceeding with message insertion`);
insertMessages(room_id);
}
});
}
}).catch(error => {
console.error('Error during AI call:', error);
return res.status(500).json({ error: 'Internal server error', details: error.message });
});
});
// Updated chatHistory endpoint to match DB table names
app.get('/chatHistory/:user_id', (req, res) => {
const user_id = req.params.user_id;
console.log(`Loading chat history for user: ${user_id}`);
// Get rooms from chat_rooms table
const sqlRooms = `SELECT * FROM ai_chat_rooms WHERE user_id = ? ORDER BY created_at DESC`;
db.query(sqlRooms, [user_id], (err, rooms) => {
if (err) {
console.error('Error loading chat rooms: ', err);
return res.status(500).json({ error: 'Database query error', details: err.message });
}
console.log(`Found ${rooms.length} chat rooms for user ${user_id}`);
// For each room, get messages
Promise.all(rooms.map(room => {
return new Promise((resolve, reject) => {
const sqlMessages = `SELECT * FROM ai_chat_messages WHERE room_id = ? ORDER BY created_at ASC`;
db.query(sqlMessages, [room.id], (err, messages) => {
if (err) {
console.error(`Error loading ai_chat_messages for room ${room.id}:`, err);
return reject(err);
}
console.log(`Found ${messages.length} ai_chat_messages for room ${room.id}`);
// Format messages for frontend
const formattedMessages = messages.map(msg => ({
id: msg.id,
role: msg.role,
content: msg.message,
model: msg.model_id,
timestamp: msg.created_at
}));
// Add messages to room object
const formattedRoom = {
id: room.id,
name: room.room_name,
messages: formattedMessages,
created_at: room.created_at,
updated_at: room.updated_at
};
resolve(formattedRoom);
});
});
}))
.then(updatedRooms => {
console.log(`Successfully loaded chat history for user ${user_id}`);
res.json({ rooms: updatedRooms });
})
.catch(err => {
console.error('Error loading messages: ', err);
return res.status(500).json({ error: 'Database query error', details: err.message });
});
});
});
// Create a new chat room
app.post('/createRoom', (req, res) => {
const { user_id, user_name, room_name } = req.body;
if (!user_id || !user_name || !room_name) {
return res.status(400).json({ error: 'Missing required parameters' });
}
const sql = `INSERT INTO ai_chat_rooms (user_id, user_name, room_name) VALUES (?, ?, ?)`;
db.query(sql, [user_id, user_name, room_name], (err, result) => {
if (err) {
console.error('Error creating chat room:', err);
return res.status(500).json({ error: 'Database error', details: err.message });
}
const newRoom = {
id: result.insertId,
user_id,
user_name,
room_name,
messages: []
};
res.status(201).json({
message: 'Chat room created successfully',
room: newRoom
});
});
});
// Delete a chat room but preserve messages
app.delete('/deleteRoom/:id', (req, res) => {
const roomId = req.params.id;
// First delete any summaries
const sqlDeleteSummaries = `DELETE FROM conversation_summaries WHERE room_id = ?`;
db.query(sqlDeleteSummaries, [roomId], (err) => {
if (err) {
console.error('Error deleting summaries:', err);
return res.status(500).json({ error: 'Database error', details: err.message });
}
// Delete the room (but keep messages)
const sqlDeleteRoom = `DELETE FROM ai_chat_rooms WHERE id = ?`;
db.query(sqlDeleteRoom, [roomId], (err, result) => {
if (err) {
console.error('Error deleting room:', err);
return res.status(500).json({ error: 'Database error', details: err.message });
}
if (result.affectedRows === 0) {
return res.status(404).json({ error: 'Room not found' });
}
// Log preserved messages
console.log(`Room ${roomId} deleted, but messages have been preserved in the database`);
res.json({
message: 'Chat room deleted successfully. Messages have been preserved.',
preservedMessages: true
});
});
});
});
// API endpoint to update room name
app.put('/updateRoomName/:roomId', (req, res) => {
const roomId = req.params.roomId;
const { room_name } = req.body;
if (!room_name || room_name.trim() === '') {
return res.status(400).json({ error: 'Room name cannot be empty' });
}
const sql = `UPDATE ai_chat_rooms SET room_name = ? WHERE id = ?`;
db.query(sql, [room_name.trim(), roomId], (err, result) => {
if (err) {
console.error('Error updating room name:', err);
return res.status(500).json({ error: 'Database error', details: err.message });
}
if (result.affectedRows === 0) {
return res.status(404).json({ error: 'Room not found' });
}
res.json({
message: 'Room name updated successfully',
room_id: roomId,
room_name: room_name.trim()
});
});
});
app.get("/progressReport/week/:uid", async (req, res) => {
// URL 파라미터로 전달된 studentUID
let uid = req.params.uid;
// 날짜를 2025년 3월 7일(금요일)로 고정
let fixedEndDateStr = "2025-03-07";
let endDate = new Date(fixedEndDateStr);
let startDate = new Date(endDate);
startDate.setDate(endDate.getDate() - 4); // 4일 차감하면 월요일 날짜
let startDateStr = startDate.toISOString().slice(0, 10);
let endDateStr = endDate.toISOString().slice(0, 10);
// datetime 범위: 월요일 00:00:00부터 금요일 23:59:59까지
let startDateTime = startDateStr + " 00:00:00";
let endDateTime = endDateStr + " 23:59:59";
// ProgressReports 테이블에서 studentUID와 submittedAt 날짜를 기준으로 주간 데이터 조회
let sql =
"SELECT studentName, assignmentCompleted, academicTrend, inClassUnderstanding, behavior, teacherComments, submittedAt " +
"FROM ProgressReports " +
"WHERE studentUID = " + mysql.escape(uid) +
" AND submittedAt BETWEEN " + mysql.escape(startDateTime) + " AND " + mysql.escape(endDateTime);
console.log(sql);
db.query(sql, async function (err, results) {
if (err) {
console.log("error", err.message, err.stack);
return res.status(500).send({ error: err.message });
}
if (results.length === 0) {
return res.send({
message: "해당 주에 progress report 데이터가 없습니다.",
weekStart: startDateStr,
weekEnd: endDateStr
});
}
// completedRate 계산: assignmentCompleted 값이 "Yes"인 경우를 카운트하여 백분율(%)
let completedCount = 0;
// academicTrend: P(Positive)=5, N(No Change)=3, D(Declining)=1
const academicMap = { "P": 5, "N": 3, "D": 1 };
// inClassUnderstanding: A=5, B=4, C=3, D=2, F=1
const understandingMap = { "A": 5, "B": 4, "C": 3, "D": 2, "F": 1 };
// behavior: O(Outstanding)=5, S(Satisfactory)=3, U(Unsatisfactory)=1
const behaviorMap = { "O": 5, "S": 3, "U": 1 };
let totalAcademic = 0,
totalUnderstanding = 0,
totalBehavior = 0;
let countReports = results.length;
let comments = [];
results.forEach(row => {
if (row.assignmentCompleted === "Yes") {
completedCount++;
}
totalAcademic += academicMap[row.academicTrend] || 0;
totalUnderstanding += understandingMap[row.inClassUnderstanding] || 0;
totalBehavior += behaviorMap[row.behavior] || 0;
if (row.teacherComments && row.teacherComments.trim() !== "") {
let commentDate = new Date(row.submittedAt).toISOString().slice(0, 10);
comments.push(commentDate + " - " + row.teacherComments);
}
});
// 완료율 계산 (백분율)
let completedRate = (completedCount / countReports) * 100;
let avgAcademic = totalAcademic / countReports;
let avgUnderstanding = totalUnderstanding / countReports;
let avgBehavior = totalBehavior / countReports;
// inClassUnderstanding 평균을 반올림하여 letter grade 결정 (5->A, 4->B, 3->C, 2->D, 1->F)
let roundedUnderstanding = Math.round(avgUnderstanding);
let understandingLetter;
switch (roundedUnderstanding) {
case 5:
understandingLetter = "A";
break;
case 4:
understandingLetter = "B";
break;
case 3:
understandingLetter = "C";
break;
case 2:
understandingLetter = "D";
break;
default:
understandingLetter = "F";
}
// letter grade에 따른 rate 매핑: A=100, B=80, C=60, D=40, F=20
const rateMap = {
"A": 100,
"B": 80,
"C": 60,
"D": 40,
"F": 20
};
let rate = rateMap[understandingLetter];
// studentName은 결과 중 첫 번째 row의 값을 사용
let studentName = results[0].studentName;
// 최종 JSON 응답 데이터 (groq summary를 추가할 예정)
let responseData = {
studentName: studentName,
weekStart: startDateStr,
weekEnd: endDateStr,
completedRate: completedRate, // 숙제 완료율(%)
averages: {
academicTrend: avgAcademic,
inClassUnderstanding: {
rate: rate, // letter grade에 대응하는 rate 값
letterGrade: understandingLetter
},
behavior: avgBehavior
},
teacherComments: comments,
rawReports: results
};
// groq SDK를 사용하여 teacherComments를 하나의 프롬프트로 결합 후 summary 요청
let prompt = comments.join("\n");
try {
const groqResponse = await groq.chat.completions.create({
messages: [
{
role: "user",
content: "Summarize the following progress report teacher comments clearly and concisely for parents. " +
"Do not include any greetings or introductory phrases like 'here is the summary' or 'here is the review'. " +
"Write as if a teacher directly wrote the comments, focusing on key insights and progress details:\n" + prompt,
},
],
model: "llama-3.3-70b-versatile",
});
let groqSummary = groqResponse.choices[0]?.message?.content || "Summary not available";
responseData.groqSummary = groqSummary;
} catch (groqErr) {
responseData.groqSummary = "Summary not available";
}
let academicTrendValue = Math.round(responseData.averages.academicTrend);
let academicTrendCircles = "";
for (let i = 1; i <= 5; i++) {
if (i <= academicTrendValue) {
academicTrendCircles += `<div class="academic rating-circle">${i}</div>\n`;
} else {
academicTrendCircles += `<div class="academic rating-circle empty">${i}</div>\n`;
}
}
let behav = Math.round(responseData.averages.behavior);
let behavcircle = "";
for (let i = 1; i <= 5; i++) {
if (i <= behav) {
behavcircle += `<div class="academic rating-circle">${i}</div>\n`;
} else {
behavcircle += `<div class="academic rating-circle empty">${i}</div>\n`;
}
}
// 이메일 템플릿 파일 읽기 및 대체
var data = fs1.readFileSync('./email_template/pr.html', 'utf-8');
var new_invoice = data.replace('{{sweek}}', responseData.weekStart)
.replace('{{eweek}}', responseData.weekEnd)
.replace('{{studentname}}', responseData.studentName)
.replace('{{aper}}', responseData.completedRate)
.replace('{{aper}}', responseData.completedRate)
.replace('{{letter}}', responseData.averages.inClassUnderstanding.letterGrade)
.replace('{{uper}}', responseData.averages.inClassUnderstanding.rate)
.replace('{{atr}}', academicTrendCircles)
.replace('{{behavior}}', behavcircle)
.replace('{{comments}}', responseData.groqSummary);
fs1.writeFileSync('./email_template/pr_temp.html', new_invoice, 'utf-8');
res.send(responseData);
});
});
app.post("/user/password/request-reset", async (req, res) => {
const { email } = req.body;
// Email validation
if (!email || !email.includes('@')) {
return res.status(400).send({ success: false, message: "Please enter a valid email address" });
}
try {
// Check if user exists
const checkUserSql = "SELECT * FROM accounts WHERE email = ?";
db.query(checkUserSql, [email], async (err, results) => {
if (err) {
console.error("Database error:", err);
return res.status(500).send({ success: false, message: "A server error occurred" });
}
// If user doesn't exist
if (results.length === 0) {
return res.status(404).send({ success: false, message: "No account found with this email address" });
}
// Generate 6-digit random code
const verificationCode = crypto.randomInt(100000, 999999).toString();
// Code creation time and expiry time (10 minutes later)
const now = new Date();
const expiry = new Date(now.getTime() + 10 * 60000); // 10 minutes later
// Delete existing code (if any)
const deleteOldCodeSql = "DELETE FROM PasswordResetCodes WHERE email = ?";
db.query(deleteOldCodeSql, [email]);
// Save new code
const insertCodeSql = `
INSERT INTO PasswordResetCodes (email, code, created_at, expires_at)
VALUES (?, ?, ?, ?)
`;
db.query(insertCodeSql, [email, verificationCode, now, expiry], async (insertErr) => {
if (insertErr) {
console.error("Code saving error:", insertErr);
return res.status(500).send({ success: false, message: "A server error occurred" });
}
try {
// 1. Read the original HTML template
const templatePath = path.join(__dirname, '../server/email_template/password_reset.html');
const tempPath = path.join(__dirname, '../server/email_template/password_reset_temp.html');
// Read original template content
const originalTemplate = fs1.readFileSync(templatePath, 'utf-8');
// 2. Create a temporary HTML file with the replacements
const resetLink = `${process.env.FRONTEND_URL || 'https://mmtprep.com'}/password/reset`;
const modifiedTemplate = originalTemplate
.replace(/{{VERIFICATION_CODE}}/g, verificationCode)
.replace(/{{RESET_LINK}}/g, resetLink);
// Write the modified content to the temporary file
fs1.writeFileSync(tempPath, modifiedTemplate, 'utf-8');
// 3. Read the temporary file for email sending
const emailHtml = fs1.readFileSync(tempPath, 'utf-8');
// 4. Set up email transporter
let transporter = nodemailer.createTransport({
host: "donotemail.mmtprep.com",
port: 465,
secure: true, // true for 465, false for other ports
auth: {
user: "donotreply@donotemail.mmtprep.com",
pass: "Mmtmmt1234!",
},
});
// 5. Send email using the temporary HTML file
let info = await transporter.sendMail({
from: '"MMT Prep" <donotreply@donotemail.mmtprep.com>',
to: email,
subject: 'Password Reset Code',
html: emailHtml
});
console.log("Password reset email sent: %s", info.messageId);
// Success response
res.send({
success: true,
message: "Password reset code has been sent to your email",
expiresIn: "10 minutes" // Provide timer info to client
});
} catch (emailErr) {
console.error("Email sending error:", emailErr);
res.status(500).send({ success: false, message: "An error occurred while sending the email" });
}
});
});
} catch (error) {
console.error("Request processing error:", error);
res.status(500).send({ success: false, message: "A server error occurred" });
}
});
app.post("/user/password/verify-code", (req, res) => {
const { email, code } = req.body;
if (!email || !code) {
return res.status(400).send({ success: false, message: "Please provide both email and verification code" });
}
// Current time
const now = new Date();
// Verify code validity
const verifySql = `
SELECT * FROM PasswordResetCodes
WHERE email = ? AND code = ? AND expires_at > ?
`;
db.query(verifySql, [email, code, now], (err, results) => {
if (err) {
console.error("Database error:", err);
return res.status(500).send({ success: false, message: "A server error occurred" });
}
if (results.length === 0) {
return res.status(400).send({
success: false,
message: "Invalid or expired verification code"
});
}
// Code verification success
res.send({
success: true,
message: "Code verified successfully",
verified: true
});
});
});
app.post("/user/password/reset", async (req, res) => {
const { email, code, newPassword } = req.body;
// Validate inputs
if (!email || !code || !newPassword) {
return res.status(400).send({
success: false,
message: "Please provide email, verification code, and new password"
});
}
// Validate password strength (minimum 8 characters)
if (newPassword.length < 8) {
return res.status(400).send({
success: false,
message: "Password must be at least 8 characters long"
});
}
try {
// Current time for code expiration check
const now = new Date();
// Step 1: Verify code validity
const verifySql = `
SELECT * FROM PasswordResetCodes
WHERE email = ? AND code = ? AND expires_at > ?
`;
db.query(verifySql, [email, code, now], async (err, results) => {
if (err) {
console.error("Database error:", err);
return res.status(500).send({ success: false, message: "A server error occurred" });
}
if (results.length === 0) {
return res.status(400).send({
success: false,
message: "Invalid or expired verification code"
});
}
try {
// Step 3: Update password in accounts table
const updateSql = "UPDATE accounts SET password = ? WHERE email = ?";
db.query(updateSql, [newPassword, email], (updateErr, updateResult) => {
if (updateErr) {
console.error("Password update error:", updateErr);
return res.status(500).send({
success: false,
message: "An error occurred while updating the password"
});
}
if (updateResult.affectedRows === 0) {
return res.status(404).send({
success: false,
message: "No account found with this email address"
});
}
// Step 4: Delete used verification code
const deleteCodeSql = "DELETE FROM PasswordResetCodes WHERE email = ?";
db.query(deleteCodeSql, [email], (deleteErr) => {
if (deleteErr) {
console.error("Code deletion error:", deleteErr);
// We don't need to fail the request if code deletion fails
}
// Step 5: Success response
res.send({
success: true,
message: "Password has been reset successfully",
redirectTo: "/login"
});
});
});
} catch (hashError) {
console.error("Password hashing error:", hashError);
res.status(500).send({
success: false,
message: "An error occurred while processing the password"
});
}
});
} catch (error) {
console.error("Request processing error:", error);
res.status(500).send({ success: false, message: "A server error occurred" });
}
});
//=================================
setInterval(() => {
const now = Date.now();
const timeout = 15000; // 15 seconds
whiteboardSessions.forEach(session => {
const { roomId, joinedUsers } = session;
let updatedUsers = joinedUsers.filter(user => {
if (now - user.lastPing <= timeout) {
return true; // User is active
} else {
// User is inactive, emit a 'user-left' event
io.to(roomId).emit('user-left', { name: user.name, userId: user.userId });
return false; // Remove user from the session
}
});
if (updatedUsers.length !== joinedUsers.length) {
session.joinedUsers = updatedUsers;
// Emit the updated active users list to all clients in the room
io.to(roomId).emit('ping-response', { activeUsers: updatedUsers });
}
});
}, 5000); // Run every 5 seconds
server.listen(port, async () => {
initializeHashMap();
console.log("SERVER IS RUNNING", port);
let info = await transporter.sendMail({
from: 'donotreply@donotemail.mmtprep.com', // sender address
to: "aiden1393@gmail.com", // list of receivers
subject: "Server is running", // Subject line
text: "The server is now running." // plain text body
});
console.log('Message sent: %s', info.messageId);
});