622 lines
21 KiB
JavaScript
622 lines
21 KiB
JavaScript
import express from 'express';
|
|
import cors from 'cors';
|
|
import path from 'path';
|
|
import { fileURLToPath } from 'url';
|
|
import mysql from 'mysql2';
|
|
import dotenv from 'dotenv';
|
|
|
|
const __filename = fileURLToPath(import.meta.url);
|
|
const __dirname = path.dirname(__filename);
|
|
|
|
// Load environment variables from server/.env (using absolute path)
|
|
const envPath = path.join(__dirname, 'server', '.env');
|
|
dotenv.config({ path: envPath });
|
|
|
|
console.log('🔍 ENV Debug Info:');
|
|
console.log(' - .env path:', envPath);
|
|
console.log(' - DB_HOST:', process.env.DB_HOST);
|
|
console.log(' - DB_NAME:', process.env.DB_NAME);
|
|
console.log(' - DB_USER:', process.env.DB_USER);
|
|
|
|
const app = express();
|
|
const PORT = process.env.PORT || 3008;
|
|
|
|
// ============================================
|
|
// DATABASE CONNECTION
|
|
// ============================================
|
|
const pool = mysql.createPool({
|
|
host: process.env.DB_HOST || 'localhost',
|
|
port: process.env.DB_PORT || 3306,
|
|
user: process.env.DB_USER || 'root',
|
|
password: process.env.DB_PASSWORD || '',
|
|
database: process.env.DB_NAME || 'db_proposalkomite',
|
|
waitForConnections: true,
|
|
connectionLimit: 10,
|
|
queueLimit: 0,
|
|
charset: 'utf8mb4'
|
|
});
|
|
|
|
const promisePool = pool.promise();
|
|
|
|
// ============================================
|
|
// DATABASE INITIALIZATION - Auto-create tables
|
|
// ============================================
|
|
const initializeDatabase = async () => {
|
|
try {
|
|
console.log('🔧 Initializing database tables...');
|
|
|
|
// Create users table
|
|
await promisePool.query(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
phone VARCHAR(50),
|
|
role VARCHAR(50) NOT NULL,
|
|
password VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
`);
|
|
console.log(' ✓ Table "users" ready');
|
|
|
|
// Create proposals table
|
|
await promisePool.query(`
|
|
CREATE TABLE IF NOT EXISTS proposals (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
title VARCHAR(500) NOT NULL,
|
|
category VARCHAR(100),
|
|
extracurricular_branch VARCHAR(255),
|
|
requester_name VARCHAR(255),
|
|
requester_id VARCHAR(50),
|
|
background TEXT,
|
|
purpose TEXT,
|
|
execution_date DATE,
|
|
mentors JSON,
|
|
participants JSON,
|
|
rab JSON,
|
|
total_requested DECIMAL(15,2) DEFAULT 0,
|
|
total_approved DECIMAL(15,2) DEFAULT 0,
|
|
status VARCHAR(50) DEFAULT 'DRAFT',
|
|
attachments JSON,
|
|
lpj_url VARCHAR(500),
|
|
lpj_data LONGTEXT,
|
|
notes TEXT,
|
|
revision_history JSON,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_status (status),
|
|
INDEX idx_requester (requester_id),
|
|
INDEX idx_category (category)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
`);
|
|
console.log(' ✓ Table "proposals" ready');
|
|
|
|
// Create settings table with LONGTEXT for storing images
|
|
await promisePool.query(`
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
setting_key VARCHAR(100) PRIMARY KEY,
|
|
setting_value LONGTEXT,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
`);
|
|
console.log(' ✓ Table "settings" ready');
|
|
|
|
// Ensure setting_value is LONGTEXT (for existing databases)
|
|
await promisePool.query(`
|
|
ALTER TABLE settings MODIFY COLUMN setting_value LONGTEXT
|
|
`).catch(() => {
|
|
// Ignore error if column is already LONGTEXT
|
|
});
|
|
|
|
// Fix category column - convert from ENUM to VARCHAR if needed
|
|
// This fixes "Data truncated for column 'category'" error
|
|
await promisePool.query(`
|
|
ALTER TABLE proposals MODIFY COLUMN category VARCHAR(100) DEFAULT 'AKADEMIK'
|
|
`).catch(() => {
|
|
// Ignore error if column type is already correct
|
|
});
|
|
console.log(' ✓ Column \"category\" ensured as VARCHAR(100)');
|
|
|
|
// Insert default settings if they don't exist
|
|
const defaultSettings = [
|
|
['academic_year', '2026'],
|
|
['kepala_sekolah_name', ''],
|
|
['kepala_sekolah_nip', ''],
|
|
['ketua_komite_name', ''],
|
|
['komite_logo', '/logo.png'],
|
|
['komite_kop', '/kop_komite.png']
|
|
];
|
|
|
|
for (const [key, value] of defaultSettings) {
|
|
await promisePool.query(`
|
|
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES (?, ?)
|
|
`, [key, value]);
|
|
}
|
|
console.log(' ✓ Default settings initialized');
|
|
|
|
console.log('✅ Database initialization complete!');
|
|
} catch (error) {
|
|
console.error('❌ Database initialization error:', error.message);
|
|
}
|
|
};
|
|
|
|
// Test database connection and initialize
|
|
pool.getConnection((err, connection) => {
|
|
if (err) {
|
|
console.error('❌ MySQL Connection Error:', err.message);
|
|
console.error('Database Config:', {
|
|
host: process.env.DB_HOST,
|
|
port: process.env.DB_PORT,
|
|
user: process.env.DB_USER,
|
|
database: process.env.DB_NAME
|
|
});
|
|
} else {
|
|
console.log('✅ MySQL Connected successfully to', process.env.DB_NAME);
|
|
connection.release();
|
|
// Initialize database tables after successful connection
|
|
initializeDatabase();
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// MIDDLEWARE
|
|
// ============================================
|
|
app.use(cors({
|
|
origin: '*',
|
|
methods: ['GET', 'POST', 'PUT', 'DELETE'],
|
|
allowedHeaders: ['Content-Type', 'Authorization']
|
|
}));
|
|
|
|
app.use(express.json({ limit: '50mb' }));
|
|
app.use(express.urlencoded({ extended: true, limit: '50mb' }));
|
|
|
|
// Request logging
|
|
app.use((req, res, next) => {
|
|
if (!req.path.startsWith('/static') && !req.path.match(/\.(js|css|png|jpg|ico)$/)) {
|
|
console.log(`${new Date().toISOString()} - ${req.method} ${req.path}`);
|
|
}
|
|
next();
|
|
});
|
|
|
|
// ============================================
|
|
// HEALTH CHECK ENDPOINT
|
|
// ============================================
|
|
app.get('/api/health', async (req, res) => {
|
|
try {
|
|
const [rows] = await promisePool.query('SELECT 1');
|
|
res.json({
|
|
status: 'ok',
|
|
server: 'E-Proposal Komite API Server',
|
|
version: '1.0.1',
|
|
database: {
|
|
status: 'connected',
|
|
type: 'MySQL',
|
|
ping: 'ok'
|
|
},
|
|
timestamp: new Date().toISOString()
|
|
});
|
|
} catch (error) {
|
|
res.status(500).json({
|
|
status: 'error',
|
|
server: 'E-Proposal Komite API Server',
|
|
database: {
|
|
status: 'disconnected',
|
|
error: error.message
|
|
},
|
|
timestamp: new Date().toISOString()
|
|
});
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// DEBUG ENDPOINT - TEMPORARY
|
|
// ============================================
|
|
app.get('/api/debug/check-password/:email', async (req, res) => {
|
|
try {
|
|
const email = req.params.email;
|
|
const [rows] = await promisePool.query(
|
|
'SELECT email, password, LENGTH(password) as pwd_len FROM users WHERE email = ?',
|
|
[email]
|
|
);
|
|
|
|
if (rows.length === 0) {
|
|
return res.json({ error: 'User not found', email });
|
|
}
|
|
|
|
const user = rows[0];
|
|
const password = user.password || '';
|
|
|
|
res.json({
|
|
email: user.email,
|
|
passwordLength: user.pwd_len,
|
|
passwordPreview: password.substring(0, 3) + '...' + password.substring(password.length - 2),
|
|
passwordCharCodes: password.split('').map(c => c.charCodeAt(0))
|
|
});
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// API ROUTES - PROPOSALS
|
|
// ============================================
|
|
app.get('/api/proposals', async (req, res, next) => {
|
|
try {
|
|
const [rows] = await promisePool.query(
|
|
'SELECT * FROM proposals ORDER BY created_at DESC'
|
|
);
|
|
|
|
const proposals = rows.map(row => ({
|
|
...row,
|
|
mentors: row.mentors ? JSON.parse(row.mentors) : [],
|
|
participants: row.participants ? JSON.parse(row.participants) : [],
|
|
rab: row.rab ? JSON.parse(row.rab) : [],
|
|
attachments: row.attachments ? JSON.parse(row.attachments) : [],
|
|
lpjData: row.lpj_data ? JSON.parse(row.lpj_data) : null,
|
|
revisionHistory: row.revision_history ? JSON.parse(row.revision_history) : [],
|
|
extracurricularBranch: row.extracurricular_branch,
|
|
requesterName: row.requester_name,
|
|
requesterId: row.requester_id,
|
|
executionDate: row.execution_date,
|
|
totalRequested: parseFloat(row.total_requested),
|
|
totalApproved: parseFloat(row.total_approved),
|
|
lpjUrl: row.lpj_url,
|
|
createdAt: row.created_at,
|
|
updatedAt: row.updated_at
|
|
}));
|
|
|
|
res.json(proposals);
|
|
} catch (error) {
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
app.get('/api/proposals/:id', async (req, res, next) => {
|
|
try {
|
|
const [rows] = await promisePool.query(
|
|
'SELECT * FROM proposals WHERE id = ?',
|
|
[req.params.id]
|
|
);
|
|
|
|
if (rows.length === 0) {
|
|
return res.status(404).json({ error: 'Proposal not found' });
|
|
}
|
|
|
|
const row = rows[0];
|
|
const proposal = {
|
|
...row,
|
|
mentors: row.mentors ? JSON.parse(row.mentors) : [],
|
|
participants: row.participants ? JSON.parse(row.participants) : [],
|
|
rab: row.rab ? JSON.parse(row.rab) : [],
|
|
attachments: row.attachments ? JSON.parse(row.attachments) : [],
|
|
lpjData: row.lpj_data ? JSON.parse(row.lpj_data) : null,
|
|
revisionHistory: row.revision_history ? JSON.parse(row.revision_history) : [],
|
|
extracurricularBranch: row.extracurricular_branch,
|
|
requesterName: row.requester_name,
|
|
requesterId: row.requester_id,
|
|
executionDate: row.execution_date,
|
|
totalRequested: parseFloat(row.total_requested),
|
|
totalApproved: parseFloat(row.total_approved),
|
|
lpjUrl: row.lpj_url,
|
|
createdAt: row.created_at,
|
|
updatedAt: row.updated_at
|
|
};
|
|
|
|
res.json(proposal);
|
|
} catch (error) {
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
app.post('/api/proposals', async (req, res, next) => {
|
|
try {
|
|
const proposal = req.body;
|
|
|
|
// Debug logging
|
|
console.log('[Server Debug] Received proposal save request:', {
|
|
id: proposal.id,
|
|
status: proposal.status,
|
|
title: proposal.title,
|
|
category: proposal.category
|
|
});
|
|
|
|
// Convert ISO date to MySQL DATE format (YYYY-MM-DD)
|
|
const formatDateForMySQL = (dateStr) => {
|
|
if (!dateStr) return null;
|
|
const date = new Date(dateStr);
|
|
return date.toISOString().split('T')[0]; // Returns YYYY-MM-DD
|
|
};
|
|
|
|
const dbData = {
|
|
id: proposal.id,
|
|
title: proposal.title,
|
|
category: proposal.category,
|
|
extracurricular_branch: proposal.extracurricularBranch || null,
|
|
requester_name: proposal.requesterName,
|
|
requester_id: proposal.requesterId,
|
|
background: proposal.background || null,
|
|
purpose: proposal.purpose || null,
|
|
execution_date: formatDateForMySQL(proposal.executionDate),
|
|
mentors: JSON.stringify(proposal.mentors || []),
|
|
participants: JSON.stringify(proposal.participants || []),
|
|
rab: JSON.stringify(proposal.rab || []),
|
|
total_requested: proposal.totalRequested || 0,
|
|
total_approved: proposal.totalApproved || 0,
|
|
status: proposal.status,
|
|
attachments: JSON.stringify(proposal.attachments || []),
|
|
lpj_url: proposal.lpjUrl || null,
|
|
lpj_data: proposal.lpjData ? JSON.stringify(proposal.lpjData) : null,
|
|
notes: proposal.notes || null,
|
|
revision_history: JSON.stringify(proposal.revisionHistory || [])
|
|
};
|
|
|
|
// Log lpj_data size for debugging
|
|
const lpjDataSize = dbData.lpj_data ? dbData.lpj_data.length : 0;
|
|
console.log('[Server Debug] Saving to DB:', {
|
|
id: dbData.id,
|
|
status: dbData.status,
|
|
lpjDataSize: `${(lpjDataSize / 1024 / 1024).toFixed(2)} MB`
|
|
});
|
|
|
|
await promisePool.query(
|
|
`INSERT INTO proposals SET ?
|
|
ON DUPLICATE KEY UPDATE
|
|
title = VALUES(title),
|
|
category = VALUES(category),
|
|
extracurricular_branch = VALUES(extracurricular_branch),
|
|
requester_name = VALUES(requester_name),
|
|
requester_id = VALUES(requester_id),
|
|
background = VALUES(background),
|
|
purpose = VALUES(purpose),
|
|
execution_date = VALUES(execution_date),
|
|
mentors = VALUES(mentors),
|
|
participants = VALUES(participants),
|
|
rab = VALUES(rab),
|
|
total_requested = VALUES(total_requested),
|
|
total_approved = VALUES(total_approved),
|
|
status = VALUES(status),
|
|
attachments = VALUES(attachments),
|
|
lpj_url = VALUES(lpj_url),
|
|
lpj_data = VALUES(lpj_data),
|
|
notes = VALUES(notes),
|
|
revision_history = VALUES(revision_history)`,
|
|
[dbData]
|
|
);
|
|
|
|
res.json({ success: true, id: proposal.id });
|
|
} catch (error) {
|
|
console.error('[Server Error] Failed to save proposal:', {
|
|
message: error.message,
|
|
code: error.code,
|
|
errno: error.errno,
|
|
sqlState: error.sqlState
|
|
});
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
app.delete('/api/proposals/:id', async (req, res, next) => {
|
|
try {
|
|
const [result] = await promisePool.query(
|
|
'DELETE FROM proposals WHERE id = ?',
|
|
[req.params.id]
|
|
);
|
|
|
|
if (result.affectedRows === 0) {
|
|
return res.status(404).json({ error: 'Proposal not found' });
|
|
}
|
|
|
|
res.json({ success: true });
|
|
} catch (error) {
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// API ROUTES - USERS
|
|
// ============================================
|
|
app.get('/api/users', async (req, res, next) => {
|
|
try {
|
|
const [rows] = await promisePool.query(
|
|
'SELECT id, name, email, phone, role, created_at, updated_at FROM users ORDER BY name ASC'
|
|
);
|
|
|
|
const users = rows.map(row => ({
|
|
id: row.id,
|
|
name: row.name,
|
|
email: row.email,
|
|
phone: row.phone,
|
|
role: row.role,
|
|
createdAt: row.created_at,
|
|
updatedAt: row.updated_at
|
|
}));
|
|
|
|
res.json(users);
|
|
} catch (error) {
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
// POST /api/users/login - User authentication
|
|
app.post('/api/users/login', async (req, res, next) => {
|
|
try {
|
|
const { email, password } = req.body;
|
|
|
|
console.log('[LOGIN] Attempt for email:', email);
|
|
|
|
if (!email || !password) {
|
|
return res.status(400).json({ error: 'Email and password are required' });
|
|
}
|
|
|
|
// First check if user exists by email
|
|
const [userCheck] = await promisePool.query(
|
|
'SELECT id, name, email, phone, role, password FROM users WHERE email = ?',
|
|
[email]
|
|
);
|
|
|
|
if (userCheck.length === 0) {
|
|
console.log('[LOGIN] No user found with email:', email);
|
|
return res.status(401).json({ error: 'Invalid email or password' });
|
|
}
|
|
|
|
// Check password match
|
|
const dbPassword = userCheck[0].password;
|
|
console.log('[LOGIN] User found. Checking password...');
|
|
console.log('[LOGIN] DB password length:', dbPassword ? dbPassword.length : 0);
|
|
console.log('[LOGIN] Input password length:', password ? password.length : 0);
|
|
|
|
if (dbPassword !== password) {
|
|
console.log('[LOGIN] Password mismatch for:', email);
|
|
return res.status(401).json({ error: 'Invalid email or password' });
|
|
}
|
|
|
|
console.log('[LOGIN] Success for:', email);
|
|
|
|
const user = {
|
|
id: userCheck[0].id,
|
|
name: userCheck[0].name,
|
|
email: userCheck[0].email,
|
|
phone: userCheck[0].phone,
|
|
role: userCheck[0].role
|
|
};
|
|
|
|
res.json({ success: true, user });
|
|
} catch (error) {
|
|
console.error('[LOGIN] Error:', error.message);
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
// POST /api/users - Create or update user
|
|
app.post('/api/users', async (req, res, next) => {
|
|
try {
|
|
const user = req.body;
|
|
|
|
if (!user.id || !user.name || !user.email || !user.role) {
|
|
return res.status(400).json({ error: 'Missing required fields' });
|
|
}
|
|
|
|
await promisePool.query(
|
|
`INSERT INTO users (id, name, email, phone, role, password)
|
|
VALUES (?, ?, ?, ?, ?, ?)
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
email = VALUES(email),
|
|
phone = VALUES(phone),
|
|
role = VALUES(role),
|
|
password = VALUES(password)`,
|
|
[user.id, user.name, user.email, user.phone || null, user.role, user.password || 'default123']
|
|
);
|
|
|
|
res.json({ success: true, id: user.id });
|
|
} catch (error) {
|
|
if (error.code === 'ER_DUP_ENTRY') {
|
|
return res.status(409).json({ error: 'Email already exists' });
|
|
}
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
// DELETE /api/users/:id - Delete user
|
|
app.delete('/api/users/:id', async (req, res, next) => {
|
|
try {
|
|
const [result] = await promisePool.query(
|
|
'DELETE FROM users WHERE id = ?',
|
|
[req.params.id]
|
|
);
|
|
|
|
if (result.affectedRows === 0) {
|
|
return res.status(404).json({ error: 'User not found' });
|
|
}
|
|
|
|
res.json({ success: true });
|
|
} catch (error) {
|
|
if (error.code === 'ER_ROW_IS_REFERENCED_2') {
|
|
return res.status(409).json({ error: 'Cannot delete user with existing proposals' });
|
|
}
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// API ROUTES - SETTINGS
|
|
// ============================================
|
|
app.get('/api/settings', async (req, res, next) => {
|
|
try {
|
|
const [rows] = await promisePool.query('SELECT * FROM settings');
|
|
|
|
const settings = {};
|
|
rows.forEach(row => {
|
|
settings[row.setting_key] = row.setting_value;
|
|
});
|
|
|
|
res.json(settings);
|
|
} catch (error) {
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
app.post('/api/settings', async (req, res, next) => {
|
|
try {
|
|
const { key, value } = req.body;
|
|
|
|
if (!key) {
|
|
return res.status(400).json({ error: 'Setting key is required' });
|
|
}
|
|
|
|
await promisePool.query(
|
|
`INSERT INTO settings (setting_key, setting_value)
|
|
VALUES (?, ?)
|
|
ON DUPLICATE KEY UPDATE
|
|
setting_value = VALUES(setting_value)`,
|
|
[key, value || '']
|
|
);
|
|
|
|
res.json({ success: true });
|
|
} catch (error) {
|
|
next(error);
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// SERVE FRONTEND (Static Files)
|
|
// ============================================
|
|
app.use(express.static(path.join(__dirname, 'dist')));
|
|
|
|
// Handle SPA routing - must be last (Express 5.x compatible)
|
|
app.use((req, res, next) => {
|
|
// Skip if requesting API routes
|
|
if (req.path.startsWith('/api')) {
|
|
return next();
|
|
}
|
|
// Serve index.html for all other routes (SPA)
|
|
res.sendFile(path.join(__dirname, 'dist', 'index.html'));
|
|
});
|
|
|
|
// ============================================
|
|
// ERROR HANDLER
|
|
// ============================================
|
|
app.use((err, req, res, next) => {
|
|
console.error('Error:', err.message);
|
|
res.status(err.status || 500).json({
|
|
error: err.message || 'Internal Server Error'
|
|
});
|
|
});
|
|
|
|
// ============================================
|
|
// START SERVER
|
|
// ============================================
|
|
app.listen(PORT, '0.0.0.0', () => {
|
|
console.log('');
|
|
console.log('🚀 E-Proposal Komite Server');
|
|
console.log('================================');
|
|
console.log(`✅ Server running on port ${PORT}`);
|
|
console.log(`🌐 Listening on: 0.0.0.0:${PORT}`);
|
|
console.log(`📡 API endpoint: http://localhost:${PORT}/api`);
|
|
console.log(`🌐 Frontend: http://localhost:${PORT}`);
|
|
console.log('');
|
|
});
|
|
|
|
export { app, promisePool as pool };
|