File: /home/mmtprep/api-mmtprep/app.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 { existsSync, mkdirSync } = require('fs'); // Add explicit import for existsSync
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 || 8081; //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 fse = require('fs-extra');
const { uploadMessageAttachments } = require('./controllers/messagesController');
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 fs.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://mmtprep.com', 'http://mmtprep.com', 'https://www.mmtprep.com', 'http://www.mmtprep.com', 'www.mmtprep.com', 'mmtprep.com', 'https://pvault.mmtprep.com', 'http://pvault.mmtprep.com', 'https://www.pvault.mmtprep.com', 'http://www.pvault.mmtprep.com', 'www.pvault.mmtprep.com', 'pvault.mmtprep.com'] }));
app.use(fileupload());
app.use(express.static("files"));
app.use(express.json({ limit: '8mb' }));
app.use(express.urlencoded({ limit: '8mb', extended: true }));
app.use(bodyParser.urlencoded({ extended: true }));
// Ensure upload directories exist
try {
// Create base upload directory
const uploadBasePath = path.join(__dirname, 'upload');
if (!existsSync(uploadBasePath)) {
mkdirSync(uploadBasePath, { recursive: true });
console.log('Created upload base directory:', uploadBasePath);
}
// Create chatroom base directory
const chatroomBasePath = path.join(uploadBasePath, 'chatroom');
if (!existsSync(chatroomBasePath)) {
mkdirSync(chatroomBasePath, { recursive: true });
console.log('Created chatroom upload directory:', chatroomBasePath);
}
// Create traditional uploads directory if it doesn't exist
const uploadsDir = path.join(__dirname, 'uploads');
if (!existsSync(uploadsDir)) {
mkdirSync(uploadsDir, { recursive: true });
console.log('Created uploads directory:', uploadsDir);
}
// Create messages uploads directory if it doesn't exist
const messageUploadsDir = path.join(uploadsDir, 'messages');
if (!existsSync(messageUploadsDir)) {
mkdirSync(messageUploadsDir, { recursive: true });
console.log('Created message uploads directory:', messageUploadsDir);
}
} catch (error) {
console.error('Error creating upload directories:', error);
}
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'));
// Add this line to serve files from the upload directory with detailed logging and absolute path
app.use('/upload', (req, res, next) => {
console.log('[STATIC_SERVE] Request for path:', req.path);
const fullPath = path.join(__dirname, 'upload');
console.log('[STATIC_SERVE] Serving from:', fullPath);
express.static(fullPath)(req, res, next);
});
app.use('/summer', require('./routes/summer'));
app.use("/classsessions", require('./routes/class_session_router'));
app.use('/', require('./routes/personal_schedule_routes'));
// Add a static route to serve uploaded message files
app.use('/uploads/messages', express.static(path.join(__dirname, 'uploads', 'messages')));
// Handle OPTIONS requests for class sessions API
app.options('/classsessions/*', (req, res) => {
res.header('Access-Control-Allow-Origin', '*');
res.header('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');
res.header('Access-Control-Allow-Headers', 'Content-Type, Authorization');
res.status(200).send();
});
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('https').Server(credentials, app);
var basic = `https://dashboard.mmtprep.com:8080`;
//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 socketToChat = {}; // 백워드 호환성을 위해 유지
// 소켓이 참여한 방을 추적하는 함수들
const joinRoom = (socket, roomId, type) => {
console.log(`[JOIN_ROOM] Socket ${socket.id} joining ${type} room: ${roomId}`);
socket.join(roomId);
// 백워드 호환성을 위해 기존 매핑도 업데이트
if (type === 'whiteboard') {
socketIdToCSIdMap[socket.id] = roomId;
} else if (type === 'chat') {
socketToChat[socket.id] = roomId;
}
}
const leaveRoom = (socket, roomId, type) => {
if (roomId) {
console.log(`[LEAVE_ROOM] Socket ${socket.id} leaving ${type} room: ${roomId}`);
socket.leave(roomId);
// 백워드 호환성을 위해 기존 매핑도 지움
if (type === 'whiteboard' && socketIdToCSIdMap[socket.id] === roomId) {
delete socketIdToCSIdMap[socket.id];
} else if (type === 'chat' && socketToChat[socket.id] === roomId) {
delete socketToChat[socket.id];
}
}
}
// 기존 매핑에서 방 ID 가져오기
const getRoomId = (socket, type) => {
if (type === 'whiteboard') {
return socketIdToCSIdMap[socket.id];
} else if (type === 'chat') {
return socketToChat[socket.id];
}
return null;
}
// isRoomMember 함수 추가
const isRoomMember = (socket, roomId) => {
return socket.rooms && socket.rooms.has(roomId);
}
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);
}
// 화이트보드 연결 유지 확인 헬퍼 함수
const ensureWhiteboardConnection = (socket) => {
const whiteboardRoomId = socketIdToCSIdMap[socket.id];
if (whiteboardRoomId) {
// 화이트보드 연결 확인
const isActive = socket.rooms && socket.rooms.has(whiteboardRoomId);
if (!isActive) {
console.log(`[ENSURE_WHITEBOARD] Re-joining whiteboard room ${whiteboardRoomId}`);
socket.join(whiteboardRoomId);
return true;
}
}
return false;
}
io.on('connection', (socket) => {
socket.on('addUser', (userId) => {
addUser(userId, socket.id);
io.emit('getUsers', users);
})
socket.on('ping', ({ roomId, userId, realUserId }) => {
const session = whiteboardSessions.find(s => s.roomId === roomId);
if (!session) return;
// Update all connections for this real user
let updated = false;
session.joinedUsers.forEach(user => {
if (user.realUserId === realUserId) {
user.lastPing = Date.now();
updated = true;
}
});
if (updated) {
// Respond with active users
socket.emit('ping-response', { activeUsers: session.joinedUsers });
}
});
socket.on('disconnect', () => {
// Check both whiteboard and chat room connections
const whiteboardRoomId = socketIdToCSIdMap[socket.id];
const chatRoomId = socketToChat[socket.id];
console.log(`[DISCONNECT] Socket ${socket.id} disconnecting. Whiteboard: ${whiteboardRoomId}, Chat: ${chatRoomId}`);
// Clean up the mappings
delete socketIdToCSIdMap[socket.id];
delete socketToChat[socket.id];
// Handle whiteboard session cleanup if this socket was part of a whiteboard
if (whiteboardRoomId) {
const sessionIndex = whiteboardSessions.findIndex(s => s.roomId === whiteboardRoomId);
if (sessionIndex !== -1) {
const session = whiteboardSessions[sessionIndex];
// Find the user by socket ID
const userIndex = session.joinedUsers.findIndex(u => u.userId === socket.id);
if (userIndex !== -1) {
const user = session.joinedUsers[userIndex];
// Check if this user has other active connections in this room
const otherConnectionsForUser = session.joinedUsers.filter(
u => u.realUserId === user.realUserId && u.userId !== socket.id
);
if (otherConnectionsForUser.length === 0) {
// This was the user's only connection, remove them from the room
session.joinedUsers.splice(userIndex, 1);
// Notify others that the user has left
socket.broadcast.to(whiteboardRoomId).emit('leave', {
name: user.name,
userId: user.userId,
realUserId: user.realUserId
});
// If the room is now empty, remove it
if (session.joinedUsers.length === 0) {
whiteboardSessions.splice(sessionIndex, 1);
} else {
// Update the active users list for remaining users
io.to(whiteboardRoomId).emit('ping-response', {
activeUsers: session.joinedUsers
});
}
}
}
}
}
// Always clean up the general users array
removeUser(socket.id);
setTimeout(() => {
io.emit('getUsers', users);
}, 750);
})
socket.on('manual-disconnect', ({ roomId, userId, realUserId }) => {
const session = whiteboardSessions.find(s => s.roomId === roomId);
if (!session) return;
// Check if this user has multiple connections
const userConnections = session.joinedUsers.filter(u => u.realUserId === realUserId);
if (userConnections.length <= 1) {
// This is the only connection, remove the user completely
const userIndex = session.joinedUsers.findIndex(u => u.realUserId === realUserId);
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,
realUserId: removedUser.realUserId
});
}
} else {
// User has multiple connections, just remove this specific connection
const connectionIndex = session.joinedUsers.findIndex(u => u.userId === userId);
if (connectionIndex !== -1) {
session.joinedUsers.splice(connectionIndex, 1);
}
}
// Emit updated active users list
io.to(roomId).emit('ping-response', { activeUsers: session.joinedUsers });
// If room is now empty, clean it up
if (session.joinedUsers.length === 0) {
whiteboardSessions = whiteboardSessions.filter(s => s.roomId !== roomId);
}
});
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, conversationId, uid, senderId, senderName, content, text, attachments, created_at, receiverId, replyTo }) => {
console.log('sendMessage', { uid, senderId, senderName, created_at, content, hasReplyData: !!replyTo, hasAttachments: attachments && attachments.length > 0 });
// Support both cs_id and conversationId for backward compatibility
const chatId = cs_id || conversationId;
const userId = uid || senderId;
const messageContent = content || text || ''; // Ensure messageContent is never undefined or null
// Convert replyTo data to match the database structure
let replyToId = null;
let replyToText = null;
let replyToSenderName = null;
if (replyTo) {
replyToId = replyTo.replyToId;
replyToText = replyTo.replyToContent;
replyToSenderName = replyTo.replyToSenderName;
}
// Process attachments to ensure compatibility
let processedAttachments = [];
if (attachments && attachments.length > 0) {
processedAttachments = attachments.map(attachment => {
// Ensure we have a standardized attachment format
return {
id: attachment.id,
link: attachment.link || attachment.url,
filename: attachment.filename || attachment.originalName,
filetype: attachment.filetype || attachment.type,
filesize: attachment.filesize || attachment.size
};
});
}
// Check if this is a valid message (either has text or attachments)
const hasText = !!messageContent && messageContent.trim() !== '';
const hasAttachments = processedAttachments.length > 0;
if (!hasText && !hasAttachments) {
console.warn('Ignoring empty message with no attachments');
return;
}
const messageData = {
mid,
cs_id: chatId,
conversationId: chatId,
uid: userId,
senderId: userId,
senderName: senderName || '', // Include sender name explicitly
content: messageContent,
text: messageContent,
attachments: processedAttachments,
created_at,
replyToId,
replyToText,
// Add replyTo for client compatibility - this structure is used in the UI
replyTo: replyTo ? {
replyToId,
replyToContent: replyToText,
replyToSenderId: replyTo.replyToSenderId || '',
replyToSenderName
} : null
};
// Broadcast to everyone in the conversation room
console.log(`Broadcasting message to room: ${chatId} from ${senderName || userId}`);
socket.to(chatId).emit('receiveMessage', messageData);
// Get all member IDs in this conversation to send notifications
try {
const getMembersQuery = 'SELECT userId FROM members WHERE conversationId = ?';
db.query(getMembersQuery, [chatId], (err, members) => {
if (err) {
console.error('Error fetching conversation members:', err);
return;
}
// For each member, send a direct notification if they're online
if (members && Array.isArray(members)) {
console.log(`Sending notifications to ${members.length} members`);
members.forEach(member => {
// Don't send notification to the sender
if (member.userId !== userId) {
// Find if user is online
const recipientUser = findUser(member.userId);
if (recipientUser && recipientUser.socketId) {
console.log(`Sending direct notification to user: ${member.userId}`);
// Send direct notification to this user's socket
io.to(recipientUser.socketId).emit('newMessageNotification', {
messageId: mid,
conversationId: chatId,
senderId: userId,
senderName: senderName || '',
content: messageContent,
created_at: created_at || new Date().toISOString(),
attachments: processedAttachments, // Include attachments in notification
replyToId,
replyToText
});
// Also send the full message directly to ensure it's received
io.to(recipientUser.socketId).emit('receiveMessage', messageData);
}
}
});
}
});
} catch (error) {
console.error('Error processing member notifications:', error);
}
// Also send to specific user if receiverId is provided (backward compatibility)
if (receiverId) {
let user = findUser(receiverId);
if (user && user.socketId) {
console.log(`Also sending message directly to user: ${receiverId}`);
socket.to(user.socketId).emit('receiveMessage', messageData);
// Also send notification
socket.to(user.socketId).emit('newMessageNotification', {
messageId: mid,
conversationId: chatId,
senderId: userId,
senderName: senderName || '',
content: messageContent,
created_at: created_at || new Date().toISOString(),
attachments: processedAttachments, // Include attachments in notification
replyToId,
replyToText
});
}
}
})
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;
// 이전 화이트보드 룸 ID 저장 (현재 연결된 화이트보드 있는지 확인)
const whiteboardRoomId = socketIdToCSIdMap[socket.id];
console.log(`[joinConversation] Current whiteboard mapping: ${whiteboardRoomId}`);
// 이전 채팅방 가져오기
const prevChatRoom = socketToChat[socket.id];
// 이전 채팅방에서 나가기
if (prevChatRoom && prevChatRoom !== csId) {
console.log(`Leaving previous chat room: ${prevChatRoom}`);
// leaveRoom 함수 사용하지 않고 직접 처리 (기존 화이트보드 세션 유지)
socket.leave(prevChatRoom);
// Also leave the conversation-prefixed room if different
if (prevChatRoom !== `conversation-${csId}`) {
socket.leave(`conversation-${prevChatRoom}`);
}
}
// 새 채팅방에 참여하기
console.log(`Joining chat room: ${csId}`);
// 기존의 socket.join(csId) 대신 joinRoom 사용
joinRoom(socket, csId, 'chat');
// Also join the conversation-prefixed room for pin events
const conversationRoom = `conversation-${csId}`;
console.log(`Also joining prefixed room: ${conversationRoom}`);
socket.join(conversationRoom);
// Broadcast call data to everyone
const user = findUserBySocketId(socket.id);
if (user) {
const call = findCallByConversationId(csId);
io.to(csId).emit('getCallData', call);
}
// 로그를 통해 현재 소켓이 참여한 모든 방 확인 (디버깅용)
console.log(`[joinConversation] Socket ${socket.id} is now in chat room ${csId}`);
console.log(`[joinConversation] Socket ${socket.id} is now in conversation room ${conversationRoom}`);
if (whiteboardRoomId) {
console.log(`[joinConversation] Socket ${socket.id} is still in whiteboard room ${whiteboardRoomId}`);
// 이 경우 화이트보드 연결이 끊기지 않도록 화이트보드 룸도 다시 조인
socket.join(whiteboardRoomId);
console.log(`[joinConversation] Re-joined whiteboard room ${whiteboardRoomId} to ensure connection`);
}
});
// Add socket handlers for pin/unpin messages
socket.on('pinMessage', (data) => {
if (!data || !data.messageId || !data.conversationId) {
console.log('Invalid data for pinMessage:', data);
return;
}
const roomId = `conversation-${data.conversationId}`;
console.log(`Broadcasting pin event to room ${roomId} for message ${data.messageId}`);
// Broadcast pin event to all users in this conversation room
socket.to(roomId).emit('messagePinned', {
messageId: data.messageId,
conversationId: data.conversationId,
pinnedBy: data.pinnedBy,
pinnedByName: data.pinnedByName,
title: data.title || 'Pinned message'
});
});
socket.on('unpinMessage', (data) => {
if (!data || !data.messageId || !data.conversationId) {
console.log('Invalid data for unpinMessage:', data);
return;
}
const roomId = `conversation-${data.conversationId}`;
console.log(`Broadcasting unpin event to room ${roomId} for message ${data.messageId}`);
// Broadcast unpin event to all users in this conversation room
socket.to(roomId).emit('messageUnpinned', {
messageId: data.messageId,
conversationId: data.conversationId
});
});
// Add socket handlers for reactions as well
socket.on('addReaction', (data) => {
if (!data || !data.messageId || !data.conversationId) {
console.log('Invalid data for addReaction:', data);
return;
}
const roomId = `conversation-${data.conversationId}`;
console.log(`Broadcasting add reaction to room ${roomId} for message ${data.messageId}`);
// Broadcast add reaction event to all users in this conversation room
socket.to(roomId).emit('messageReaction', {
messageId: data.messageId,
userId: data.userId,
userName: data.userName,
reaction: data.reaction,
action: 'add',
reactionId: data.reactionId
});
});
socket.on('removeReaction', (data) => {
if (!data || !data.messageId || !data.conversationId) {
console.log('Invalid data for removeReaction:', data);
return;
}
const roomId = `conversation-${data.conversationId}`;
console.log(`Broadcasting remove reaction to room ${roomId} for message ${data.messageId}`);
// Broadcast remove reaction event to all users in this conversation room
socket.to(roomId).emit('messageReaction', {
messageId: data.messageId,
userId: data.userId,
reaction: data.reaction,
action: 'remove'
});
});
// 화이트보드로 돌아가는 이벤트 핸들러 추가
socket.on('returnToWhiteboard', () => {
const whiteboardRoomId = socketIdToCSIdMap[socket.id];
if (whiteboardRoomId) {
console.log(`[returnToWhiteboard] Ensuring connection to whiteboard room: ${whiteboardRoomId}`);
socket.join(whiteboardRoomId);
// 세션 찾기
const session = whiteboardSessions.find(s => s.roomId === whiteboardRoomId);
if (session) {
// 접속 유저 목록 다시 보내기
socket.emit('joinedUserinwhiteboardSession', { joinedUsers: session.joinedUsers });
if (session.imageLockActive !== undefined) {
socket.emit('imageLockChanged', session.imageLockActive, null, null);
}
}
// 성공 응답 보내기
socket.emit('whiteboard-reconnected', { status: 'success', roomId: whiteboardRoomId });
} else {
socket.emit('whiteboard-reconnected', { status: 'error', message: 'No whiteboard session found' });
}
});
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, isRefresh }) => {
// 필수 데이터가 없는 경우, 이벤트 처리 중단
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, isRefresh });
// Associate this socket ID with the room for OPTIONS request handling
socketIdToCSIdMap[socket.id] = roomId;
// Join the socket to the room
socket.join(roomId);
let session = whiteboardSessions.find((el) => el.roomId === roomId);
if (session) {
// Check if this user is already in the session (by realUserId)
let currentUser = session.joinedUsers.find((el) => el.realUserId === realUserId);
if (currentUser) {
// User is already in the session - update their details and last ping
// Store the previous socket ID to disconnect it later if necessary
const previousSocketId = currentUser.userId;
session.joinedUsers = session.joinedUsers.map(el => {
if (el.realUserId === realUserId) {
// If socket ID changed (new device/tab), handle the previous connection
if (previousSocketId !== userId && io.sockets.sockets.get(previousSocketId)) {
// Don't disconnect the socket, just update the userId - this allows multiple tabs/devices
console.log(`User ${realUserId} connected from a new device/tab, updating socket ID`);
}
return { name, userId, realUserId, lastPing: Date.now(), role };
} else {
return el;
}
});
} else {
// New user to this session - add them
session.joinedUsers.push({ name, userId, realUserId, lastPing: Date.now(), role });
}
// Send the updated user list
io.to(roomId).emit('joinedUserinwhiteboardSession', { joinedUsers: session.joinedUsers });
} else {
// Create a new session for this room
whiteboardSessions.push({
roomId,
joinedUsers: [{ name, userId, realUserId, lastPing: Date.now(), role }],
imageLockActive: false // Default to unlocked
});
io.to(userId).emit('joinedUserinwhiteboardSession', {
joinedUsers: [{ name, userId, realUserId, lastPing: Date.now(), role }]
});
}
// Send the current image lock state to the newly joined user
// Use the already defined session object
if (session && session.imageLockActive !== undefined) {
// Include null for fromUserId and fromSocketId to ensure client doesn't filter it
// as it's an initial state message, not an update from another user
socket.emit('imageLockChanged', session.imageLockActive, null, null);
}
// Emit an event to tell the client to refresh objects if this is a refresh
if (isRefresh) {
socket.emit('refresh-confirmed', { status: 'success', message: 'Refresh detected. Load fresh objects.' });
}
// Notify others of the new user
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, user, tempRestore }) => {
let connection;
try {
// Get connection from pool
connection = await new Promise((resolve, reject) => {
db.getConnection((err, conn) => {
if (err) reject(err);
else resolve(conn);
});
});
// Start transaction
await new Promise((resolve, reject) => {
connection.beginTransaction(err => {
if (err) reject(err);
else resolve();
});
});
// Check if cs_id exists
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) {
await new Promise((resolve, reject) => {
connection.rollback(() => {
resolve();
});
});
console.error(`Class session ID ${roomId} does not exist. Cannot add object.`);
return;
}
// Check if object already exists
const objectExists = await new Promise((resolve, reject) => {
const checkObjectQuery = `SELECT id FROM class_session_objects WHERE id = ? AND cs_id = ?`;
connection.query(checkObjectQuery, [objectId, roomId], (err, results) => {
if (err) reject(err);
else resolve(results.length > 0);
});
});
if (objectExists) {
await new Promise((resolve, reject) => {
connection.rollback(() => {
resolve();
});
});
console.log(`Object ${objectId} already exists for room ${roomId}. Skipping insertion.`);
return;
}
// Format object string
let objectString;
try {
objectString = typeof object === 'string'
? object
: JSON.stringify(object);
} catch (err) {
console.error('Error stringifying object:', err);
throw err;
}
// Insert object
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);
});
});
// Set rid and store in HashMap
const rid = result.insertId;
hashMap.set(objectId, rid);
// Commit transaction
await new Promise((resolve, reject) => {
connection.commit(err => {
if (err) reject(err);
else resolve();
});
});
// Broadcast event based on whether this is a restoration or new addition
if (tempRestore) {
// For restored objects, emit special event
socket.broadcast.to(roomId).emit('object-restored', {
roomId,
object,
objectId,
user
});
} else {
// For new objects, use regular object-added event
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('imageLockChanged', ({ roomId, isImageLockActive, userId, realUserId }) => {
// Find the session
const session = whiteboardSessions.find((el) => el.roomId === roomId);
if (!session) {
return;
}
// Update session state
session.imageLockActive = isImageLockActive;
// Broadcast to all users in room except the sender
socket.to(roomId).emit('imageLockChanged', {
isImageLockActive,
userId,
realUserId
});
// Handle same user on different devices
const sameUserDifferentSockets = session.joinedUsers.filter(user =>
user.realUserId === realUserId && user.userId !== userId
);
// Send to other devices of the same user with null IDs to bypass filtering
sameUserDifferentSockets.forEach(userDevice => {
if (io.sockets.sockets.get(userDevice.userId)) {
io.to(userDevice.userId).emit('imageLockChanged', {
isImageLockActive,
userId: null,
realUserId: null
});
}
});
});
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.' });
});
});
});
// Modified upload route for file attachments including chat attachments
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;
}
// Check if this is a chat attachment upload
if (req.body.chatId && req.body.messageId) {
try {
// Create chat directory structure: server/chat/chatId/files/
const chatDir = path.join(__dirname, 'chat', req.body.chatId, 'files');
// Ensure directory exists
if (!fs1.existsSync(chatDir)) {
fs1.mkdirSync(chatDir, { recursive: true });
}
const files = Array.isArray(req.files.files) ? req.files.files : [req.files.files];
const fileUrls = [];
// Process each file and save it to the proper location
for (const file of files) {
// Create unique filename to prevent collisions
const fileExt = path.extname(file.name);
const fileName = `${Date.now()}_${path.basename(file.name, fileExt)}${fileExt}`;
const filePath = path.join(chatDir, fileName);
// Move the file to the appropriate directory
await file.mv(filePath);
// Create a URL for the file
const fileUrl = `/chat/${req.body.chatId}/files/${fileName}`;
// Insert attachment info into message_attachments table
const insertQuery = `INSERT INTO message_attachments (mid, link, filename, filetype, filesize)
VALUES (?, ?, ?, ?, ?)`;
await new Promise((resolve, reject) => {
db.query(
insertQuery,
[req.body.messageId, fileUrl, file.name, file.mimetype, file.size],
(err, result) => {
if (err) {
console.error('Error inserting attachment record:', err);
reject(err);
} else {
resolve(result);
}
}
);
});
// Add file info to response
fileUrls.push({
url: fileUrl,
originalName: file.name,
type: file.mimetype,
size: file.size
});
}
res.status(200).json({ fileUrls });
} catch (error) {
console.error('Error processing chat file upload:', error);
res.status(500).send('Error processing file upload');
}
} else {
// Original upload behavior for non-chat files
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);
});
}
});
// Route to serve chat attachment files
app.get('/chat/:chatId/files/:fileName', function (req, res) {
const chatId = req.params.chatId;
const fileName = req.params.fileName;
if (!chatId || !fileName) {
return res.status(400).send('Missing parameters');
}
const filePath = path.join(__dirname, 'chat', chatId, 'files', fileName);
// Check if file exists
if (!fs1.existsSync(filePath)) {
return res.status(404).send('File not found');
}
// Determine content type based on file extension
const ext = path.extname(fileName).toLowerCase();
let contentType = 'application/octet-stream'; // Default content type
// Set content type based on common file extensions
if (ext === '.jpg' || ext === '.jpeg') contentType = 'image/jpeg';
else if (ext === '.png') contentType = 'image/png';
else if (ext === '.gif') contentType = 'image/gif';
else if (ext === '.pdf') contentType = 'application/pdf';
else if (ext === '.txt') contentType = 'text/plain';
else if (ext === '.doc' || ext === '.docx') contentType = 'application/msword';
// Send file with appropriate content type
res.setHeader('Content-Type', contentType);
res.sendFile(filePath);
});
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) => {
// Show all invoices except Save (10) and Discard (2) status to parents
var sql = 'SELECT * FROM invoice WHERE c_uid = ' + mysql.escape(req.session.uid) + ' AND status NOT IN ("10", "2")';
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);
});
// Add this route before the app.listen() call
app.get('/debug/conversations/:userId', async (req, res) => {
const userId = req.params.userId;
console.log(`DEBUG: Fetching conversations for user: ${userId}`);
try {
// First check if the user is in the members table
const memberQuery = "SELECT conversationId FROM members WHERE userId = ?";
db.query(memberQuery, [userId], (memberErr, memberResults) => {
if (memberErr) {
console.error('Error checking member status:', memberErr);
return res.status(500).json({ error: memberErr.message });
}
console.log(`User ${userId} is a member of ${memberResults.length} conversations`);
// Now check if the user created any conversations
const creatorQuery = "SELECT conversationId FROM conversations WHERE createdBy = ?";
db.query(creatorQuery, [userId], (creatorErr, creatorResults) => {
if (creatorErr) {
console.error('Error checking creator status:', creatorErr);
return res.status(500).json({ error: creatorErr.message });
}
console.log(`User ${userId} created ${creatorResults.length} conversations`);
// Return detailed debug info
res.json({
message: "Conversation debug info",
userId: userId,
memberOf: memberResults,
createdConversations: creatorResults,
totalConversations: memberResults.length + creatorResults.length
});
});
});
} catch (err) {
console.error('Error in debug endpoint:', err);
res.status(500).json({ error: err.message });
}
});
// Modified upload route for chat attachments
app.post('/upload/chat', async function (req, res) {
if (!req.files || Object.keys(req.files).length === 0) {
return res.status(400).send('No files were uploaded.');
}
try {
const chatId = req.body.chatId;
const messageId = req.body.messageId;
if (!chatId || !messageId) {
return res.status(400).send('Missing chatId or messageId');
}
// Create directory structure: server/upload/chatroom/chatId/
const chatDir = path.join(__dirname, 'upload', 'chatroom', chatId);
await fse.ensureDir(chatDir); // This will create the directory if it doesn't exist
const files = Array.isArray(req.files.files) ? req.files.files : [req.files.files];
const fileUrls = [];
// Process each file and save it to the proper location
for (const file of files) {
// Create unique filename to prevent collisions
const fileExt = path.extname(file.name);
const fileName = `${Date.now()}_${path.basename(file.name, fileExt)}${fileExt}`;
const filePath = path.join(chatDir, fileName);
// Move the file to the appropriate directory
await file.mv(filePath);
// Create a URL for the file
const fileUrl = `/upload/chatroom/${chatId}/${fileName}`;
// Insert attachment info into message_attachments table
const insertQuery = `INSERT INTO message_attachments (mid, link, filename, filetype, filesize)
VALUES (?, ?, ?, ?, ?)`;
await new Promise((resolve, reject) => {
db.query(
insertQuery,
[messageId, fileUrl, file.name, file.mimetype, file.size],
(err, result) => {
if (err) {
console.error('Error inserting attachment record:', err);
reject(err);
} else {
resolve(result);
}
}
);
});
// Add file info to response
fileUrls.push({
url: fileUrl,
originalName: file.name,
type: file.mimetype,
size: file.size
});
}
res.status(200).json({ fileUrls });
} catch (error) {
console.error('Error processing file upload:', error);
res.status(500).send('Error processing file upload');
}
});
// Route to serve chat attachment files - for the new path structure
app.get('/upload/chatroom/:chatId/:fileName', function (req, res) {
const chatId = req.params.chatId;
const fileName = req.params.fileName;
if (!chatId || !fileName) {
console.error('[FILE_SERVE] Missing parameters for file request');
return res.status(400).send('Missing parameters');
}
const filePath = path.join(__dirname, 'upload', 'chatroom', chatId, fileName);
console.log(`[FILE_SERVE] Attempting to serve file: ${filePath}`);
// Check if file exists
if (!fs1.existsSync(filePath)) {
console.error(`[FILE_SERVE] File not found: ${filePath}`);
return res.status(404).send('File not found');
}
// Determine content type based on file extension
const ext = path.extname(fileName).toLowerCase();
let contentType = 'application/octet-stream'; // Default content type
// Set content type based on common file extensions
if (ext === '.jpg' || ext === '.jpeg') contentType = 'image/jpeg';
else if (ext === '.png') contentType = 'image/png';
else if (ext === '.gif') contentType = 'image/gif';
else if (ext === '.pdf') contentType = 'application/pdf';
else if (ext === '.txt') contentType = 'text/plain';
else if (ext === '.doc' || ext === '.docx') contentType = 'application/msword';
// Send file with appropriate content type
console.log(`[FILE_SERVE] Serving file with content type: ${contentType}`);
res.setHeader('Content-Type', contentType);
res.sendFile(filePath);
});
// Ensure upload directories exist
app.use(async (req, res, next) => {
try {
// Create base upload directory
const uploadBasePath = path.join(__dirname, 'upload');
if (!fs1.existsSync(uploadBasePath)) {
fs1.mkdirSync(uploadBasePath, { recursive: true });
}
// Create chatroom base directory
const chatroomBasePath = path.join(uploadBasePath, 'chatroom');
if (!fs1.existsSync(chatroomBasePath)) {
fs1.mkdirSync(chatroomBasePath, { recursive: true });
}
next();
} catch (error) {
console.error('Error creating upload directories:', error);
next();
}
});
// Modify the route to get conversation messages to include attachments
app.get('/conversation/:id/messages', (req, res) => {
const conversationId = req.params.id;
const limit = parseInt(req.query.limit) || 50; // Default limit to 50 if not specified
const offset = parseInt(req.query.offset) || 0; // Default offset to 0 if not specified
if (!conversationId) {
return res.status(400).json({ error: 'Conversation ID is required' });
}
// Get paginated messages for this conversation
const getMessagesSql = `
SELECT * FROM messages
WHERE conversationId = ?
ORDER BY messageCreated DESC
LIMIT ? OFFSET ?
`;
db.query(getMessagesSql, [conversationId, limit, offset], (err, messages) => {
if (err) {
console.error('Error fetching messages:', err);
return res.status(500).json({ error: 'Database error' });
}
// If no messages found, return an empty array
if (!messages || messages.length === 0) {
return res.json([]);
}
// Get message IDs to fetch attachments
const messageIds = messages.map(msg => msg.messageId);
// Fetch attachments for these messages
const getAttachmentsSql = `SELECT * FROM message_attachments WHERE mid IN (?)`;
db.query(getAttachmentsSql, [messageIds], (attachErr, attachments) => {
if (attachErr) {
console.error('Error fetching attachments:', attachErr);
// Continue without attachments
return res.json(messages);
}
// Group attachments by message ID
const attachmentsByMessageId = {};
if (attachments && attachments.length > 0) {
attachments.forEach(attachment => {
if (!attachmentsByMessageId[attachment.mid]) {
attachmentsByMessageId[attachment.mid] = [];
}
attachmentsByMessageId[attachment.mid].push({
id: attachment.id,
link: attachment.link,
filename: attachment.filename,
filetype: attachment.filetype,
filesize: attachment.filesize
});
});
}
// Add attachments to messages
const messagesWithAttachments = messages.map(message => {
return {
...message,
attachments: attachmentsByMessageId[message.messageId] || []
};
});
// Add an additional field to indicate if there are more messages available
// Get total count of messages in this conversation
const countSql = `SELECT COUNT(*) as total FROM messages WHERE conversationId = ?`;
db.query(countSql, [conversationId], (countErr, countResult) => {
if (countErr) {
console.error('Error counting messages:', countErr);
// Just return the messages without the count info
return res.json(messagesWithAttachments);
}
const totalMessages = countResult[0].total;
const hasMore = totalMessages > (offset + limit);
// Send response with pagination metadata
res.json({
messages: messagesWithAttachments,
pagination: {
total: totalMessages,
offset,
limit,
hasMore
}
});
});
});
});
});
// Ensure uploads directory exists
const uploadsDir = path.join(__dirname, 'uploads', 'messages');
if (!existsSync(uploadsDir)) {
mkdirSync(uploadsDir, { recursive: true });
console.log('Created uploads directory:', uploadsDir);
}
// Add direct route for message file uploads
app.post('/upload/message', uploadMessageAttachments);
// Endpoint to handle file uploads for messages
app.post('/messages/upload', async (req, res) => {
if (!req.files || Object.keys(req.files).length === 0) {
return res.status(400).json({ error: 'No files were uploaded.' });
}
try {
console.log("Received request to /messages/upload - forwarding to controller");
// Forward to the controller function which handles all the file upload logic
return uploadMessageAttachments(req, res);
} catch (error) {
console.error('Error in message upload endpoint:', error);
res.status(500).json({
error: 'Internal server error',
message: error.message
});
}
});
// Route to serve message uploads - add this to support old paths
app.get('/uploads/messages/:messageId/:fileName', function (req, res) {
const messageId = req.params.messageId;
const fileName = req.params.fileName;
if (!messageId || !fileName) {
return res.status(400).send('Missing parameters');
}
// First check if file exists in the old path
const oldFilePath = path.join(__dirname, 'uploads', 'messages', messageId, fileName);
// If exists in old path, serve it
if (fs1.existsSync(oldFilePath)) {
// Determine content type based on file extension
const ext = path.extname(fileName).toLowerCase();
let contentType = 'application/octet-stream'; // Default content type
// Set content type based on common file extensions
if (ext === '.jpg' || ext === '.jpeg') contentType = 'image/jpeg';
else if (ext === '.png') contentType = 'image/png';
else if (ext === '.gif') contentType = 'image/gif';
else if (ext === '.pdf') contentType = 'application/pdf';
else if (ext === '.txt') contentType = 'text/plain';
else if (ext === '.doc' || ext === '.docx') contentType = 'application/msword';
// Send file with appropriate content type
res.setHeader('Content-Type', contentType);
return res.sendFile(oldFilePath);
}
// If not found in old path, check if it's in the new path
// Extract chatId and base filename from the message ID and filename
const fileParts = fileName.split('_');
if (fileParts.length >= 2) {
const chatId = messageId; // In the new system, we use chatId instead of messageId for directory
const newFilePath = path.join(__dirname, 'upload', 'chatroom', chatId, fileName);
if (fs1.existsSync(newFilePath)) {
// Determine content type based on file extension
const ext = path.extname(fileName).toLowerCase();
let contentType = 'application/octet-stream'; // Default content type
// Set content type based on common file extensions
if (ext === '.jpg' || ext === '.jpeg') contentType = 'image/jpeg';
else if (ext === '.png') contentType = 'image/png';
else if (ext === '.gif') contentType = 'image/gif';
else if (ext === '.pdf') contentType = 'application/pdf';
else if (ext === '.txt') contentType = 'text/plain';
else if (ext === '.doc' || ext === '.docx') contentType = 'application/msword';
// Send file with appropriate content type
res.setHeader('Content-Type', contentType);
return res.sendFile(newFilePath);
}
}
// If not found in either location, return 404
console.error(`File not found in either location: ${oldFilePath}`);
return res.status(404).send('File not found');
});
// Debug route to check if a file exists and show its path
app.get('/debug/file', (req, res) => {
const filepath = req.query.path;
if (!filepath) {
return res.status(400).json({ error: 'No file path provided' });
}
// First check as absolute path
if (fs1.existsSync(filepath)) {
return res.json({
exists: true,
path: filepath,
type: 'absolute'
});
}
// Then check relative to server directory
const serverPath = path.join(__dirname, filepath);
if (fs1.existsSync(serverPath)) {
return res.json({
exists: true,
path: serverPath,
type: 'server-relative'
});
}
// Check in uploads
const uploadsPath = path.join(__dirname, 'uploads', filepath);
if (fs1.existsSync(uploadsPath)) {
return res.json({
exists: true,
path: uploadsPath,
type: 'uploads-relative'
});
}
// Check in upload
const uploadPath = path.join(__dirname, 'upload', filepath);
if (fs1.existsSync(uploadPath)) {
return res.json({
exists: true,
path: uploadPath,
type: 'upload-relative'
});
}
// File not found
res.status(404).json({
exists: false,
checkedPaths: [
filepath,
serverPath,
uploadsPath,
uploadPath
]
});
});
// Debug route to list all files in a chatroom
app.get('/debug/chatfiles/:chatId', async (req, res) => {
try {
const chatId = req.params.chatId;
if (!chatId) {
return res.status(400).json({ error: 'Chat ID is required' });
}
const chatDir = path.join(__dirname, 'upload', 'chatroom', chatId);
console.log(`Checking for files in: ${chatDir}`);
// Check if directory exists
if (!fs1.existsSync(chatDir)) {
return res.json({
exists: false,
path: chatDir,
message: 'Chat directory does not exist'
});
}
// Read all files in the directory
const files = fs1.readdirSync(chatDir);
// Get file details
const fileDetails = files.map(file => {
const filePath = path.join(chatDir, file);
const stats = fs1.statSync(filePath);
const urlPath = `/upload/chatroom/${chatId}/${file}`;
const absoluteUrl = `${req.protocol}://${req.get('host')}${urlPath}`;
return {
name: file,
path: filePath,
url: urlPath,
absoluteUrl,
size: stats.size,
created: stats.birthtime,
testLink: `<a href="${urlPath}" target="_blank">Test Link</a>`,
testAbsoluteLink: `<a href="${absoluteUrl}" target="_blank">Test Absolute Link</a>`
};
});
// Return the file information
res.json({
chatId,
dirPath: chatDir,
fileCount: files.length,
files: fileDetails
});
} catch (error) {
console.error('Error in debug/chatfiles:', error);
res.status(500).json({ error: error.message });
}
});
// Holiday Management Routes
// Get all holidays
app.get('/holiday/get/all', (req, res) => {
const sql = 'SELECT * FROM holiday ORDER BY month, day';
db.query(sql, (err, results) => {
if (err) {
console.error('Error fetching holidays:', err);
return res.status(500).json({ error: 'Database query error', details: err.message });
}
res.status(200).json({ holidays: results });
});
});
// Add new holiday
app.post('/holiday/add', (req, res) => {
const { name, note, year, month, day, endMonth, endDay } = req.body;
if (!name || !year || !month || !day) {
return res.status(400).json({ error: 'Name, year, month, and day are required' });
}
if (month < 1 || month > 12 || day < 1 || day > 31) {
return res.status(400).json({ error: 'Invalid month or day values' });
}
// Validate end date if provided
if (endMonth || endDay) {
if (!endMonth || !endDay) {
return res.status(400).json({ error: 'If end date is specified, both end month and end day are required' });
}
if (endMonth < 1 || endMonth > 12 || endDay < 1 || endDay > 31) {
return res.status(400).json({ error: 'Invalid end month or end day values' });
}
// Check if end date is after start date
const startDate = new Date(year, month - 1, day);
const endDate = new Date(year, endMonth - 1, endDay);
if (endDate < startDate) {
return res.status(400).json({ error: 'End date must be after start date' });
}
}
const sql = 'INSERT INTO holiday (name, note, year, month, day, end_month, end_day) VALUES (?, ?, ?, ?, ?, ?, ?)';
const values = [name, note || null, year, month, day, endMonth || null, endDay || null];
db.query(sql, values, (err, result) => {
if (err) {
console.error('Error inserting holiday:', err);
if (err.code === 'ER_DUP_ENTRY') {
return res.status(409).json({ error: 'A holiday already exists with this name and date' });
}
return res.status(500).json({ error: 'Database insert error', details: err.message });
}
res.status(201).json({ message: 'Holiday added successfully', holidayId: result.insertId });
});
});
// Update holiday
app.post('/holiday/update', (req, res) => {
const { id, name, note, year, month, day, endMonth, endDay } = req.body;
if (!id || !name || !year || !month || !day) {
return res.status(400).json({ error: 'ID, name, year, month, and day are required' });
}
if (month < 1 || month > 12 || day < 1 || day > 31) {
return res.status(400).json({ error: 'Invalid month or day values' });
}
// Validate end date if provided
if (endMonth || endDay) {
if (!endMonth || !endDay) {
return res.status(400).json({ error: 'If end date is specified, both end month and end day are required' });
}
if (endMonth < 1 || endMonth > 12 || endDay < 1 || endDay > 31) {
return res.status(400).json({ error: 'Invalid end month or end day values' });
}
// Check if end date is after start date
const startDate = new Date(year, month - 1, day);
const endDate = new Date(year, endMonth - 1, endDay);
if (endDate < startDate) {
return res.status(400).json({ error: 'End date must be after start date' });
}
}
const sql = 'UPDATE holiday SET name = ?, note = ?, year = ?, month = ?, day = ?, end_month = ?, end_day = ? WHERE id = ?';
const values = [name, note || null, year, month, day, endMonth || null, endDay || null, id];
db.query(sql, values, (err, result) => {
if (err) {
console.error('Error updating holiday:', err);
if (err.code === 'ER_DUP_ENTRY') {
return res.status(409).json({ error: 'A holiday already exists with this name and date' });
}
return res.status(500).json({ error: 'Database update error', details: err.message });
}
if (result.affectedRows === 0) {
return res.status(404).json({ error: 'Holiday not found' });
}
res.status(200).json({ message: 'Holiday updated successfully' });
});
});
// Delete holiday
app.post('/holiday/delete', (req, res) => {
const { id } = req.body;
if (!id) {
return res.status(400).json({ error: 'Holiday ID is required' });
}
const sql = 'DELETE FROM holiday WHERE id = ?';
db.query(sql, [id], (err, result) => {
if (err) {
console.error('Error deleting holiday:', err);
return res.status(500).json({ error: 'Database delete error', details: err.message });
}
if (result.affectedRows === 0) {
return res.status(404).json({ error: 'Holiday not found' });
}
res.status(200).json({ message: 'Holiday deleted successfully' });
});
});