const path = require('path');
const fs = require('fs');
module.exports = function(app, pool) {
// Helper to log activity
async function logActivity(userId, projectId, actionType, details) {
try {
await pool.query(
"INSERT INTO activity_logs (user_id, project_id, action, details) VALUES (?, ?, ?, ?)",
[userId, projectId, actionType, details]
);
} catch (err) {
console.error('Failed to log activity:', err);
}
}
// Auth Middleware Helper (minimal wrapper matching server.js style)
function requireAuth(req, res, next) {
if (!req.headers.authorization) {
return res.status(401).json({ error: 'Unauthorized' });
}
// In this server, token validation is usually done via a jwt verify.
// For simplicity, we can extract the user info from req.user if it is already populated,
// or parse the token directly.
if (req.user) {
return next();
}
// Fallback to decode token
try {
const token = req.headers.authorization.split(' ')[1];
const jwt = require('jsonwebtoken');
const JWT_SECRET = process.env.JWT_SECRET || 'b2b-secret-key-12345';
const decoded = jwt.verify(token, JWT_SECRET);
req.user = decoded;
next();
} catch (err) {
return res.status(401).json({ error: 'Invalid Token' });
}
}
// 1. Get all PRs for a project
app.get('/api/projects/:id/prs', requireAuth, async (req, res) => {
try {
const projectId = req.params.id;
const [prs] = await pool.query(
"SELECT pr.*, u.username as creator_name FROM project_prs pr LEFT JOIN users u ON pr.created_by = u.id WHERE pr.project_id = ? ORDER BY pr.id DESC",
[projectId]
);
res.json(prs);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 2. Get a single PR and its items
app.get('/api/prs/:id', requireAuth, async (req, res) => {
try {
const prId = req.params.id;
const [prs] = await pool.query("SELECT * FROM project_prs WHERE id = ?", [prId]);
if (prs.length === 0) return res.status(404).json({ error: 'PR not found' });
const [items] = await pool.query(
"SELECT pri.*, s.name as supplier_name FROM project_pr_items pri LEFT JOIN suppliers s ON pri.supplier_id = s.id WHERE pri.pr_id = ?",
[prId]
);
res.json({ pr: prs[0], items });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 3. Get all PRs (global list for Procurement/Reviewers/Executives)
app.get('/api/prs', requireAuth, async (req, res) => {
try {
const [prs] = await pool.query(
"SELECT pr.*, p.name as project_name, u.username as creator_name FROM project_prs pr JOIN projects p ON pr.project_id = p.id LEFT JOIN users u ON pr.created_by = u.id ORDER BY pr.id DESC"
);
res.json(prs);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 4. Create a PR from Project BOM
app.post('/api/projects/:id/prs', requireAuth, async (req, res) => {
const projectId = req.params.id;
const { target_delivery_date, items } = req.body;
const userId = req.user.id;
if (!items || items.length === 0) {
return res.status(400).json({ error: 'No items provided' });
}
try {
const result = await pool.query(
"INSERT INTO project_prs (project_id, created_by, target_delivery_date, status) VALUES (?, ?, ?, 'pending_pricing')",
[projectId, userId, target_delivery_date]
);
const prId = result.insertId;
for (const item of items) {
await pool.query(
"INSERT INTO project_pr_items (pr_id, description, brand, model, qty, unit, target_delivery_date, system_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
[prId, item.description, item.brand, item.model, item.qty, item.unit, item.target_delivery_date || target_delivery_date, item.system_type || 'Other']
);
}
await logActivity(userId, projectId, 'PR_CREATE', `Created PR #${prId} with ${items.length} items`);
res.status(201).json({ id: prId, message: 'PR created successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 5. Procurement updates sourced prices
app.put('/api/prs/:id/items', requireAuth, async (req, res) => {
const prId = req.params.id;
const { items } = req.body; // Array of { id, sourced_price, supplier_id }
const userId = req.user.id;
if (!items || items.length === 0) {
return res.status(400).json({ error: 'No items provided' });
}
try {
for (const item of items) {
await pool.query(
"UPDATE project_pr_items SET sourced_price = ?, supplier_id = ?, status = 'sourced' WHERE id = ? AND pr_id = ?",
[item.sourced_price, item.supplier_id, item.id, prId]
);
}
// Update PR status to pending manager review
await pool.query(
"UPDATE project_prs SET status = 'pending_manager_review', updated_at = CURRENT_TIMESTAMP WHERE id = ?",
[prId]
);
await logActivity(userId, null, 'PR_SOURCE_PRICES', `Sourced prices for PR #${prId}`);
res.json({ message: 'Sourced prices updated successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 6. Manager Review (EE or ME Manager)
app.post('/api/prs/:id/review', requireAuth, async (req, res) => {
const prId = req.params.id;
const { approve, system_type, remarks } = req.body; // approve: boolean, system_type: 'EE' or 'ME'
const userId = req.user.id;
const userRole = req.user.role;
// Validation of role permission
if (system_type === 'EE' && userRole !== 'ee_manager' && userRole !== 'admin' && userRole !== 'superadmin') {
return res.status(403).json({ error: 'Only EE Manager can approve EE items' });
}
if (system_type === 'ME' && userRole !== 'me_manager' && userRole !== 'admin' && userRole !== 'superadmin') {
return res.status(403).json({ error: 'Only ME Manager can approve ME items' });
}
try {
const approvalColumn = system_type === 'EE' ? 'ee_manager_approved' : 'me_manager_approved';
const val = approve ? 1 : -1; // 1 = approved, -1 = rejected
await pool.query(
`UPDATE project_prs SET ${approvalColumn} = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
[val, prId]
);
// Fetch current PR states to see if all required manager reviews are complete
const [prs] = await pool.query("SELECT * FROM project_prs WHERE id = ?", [prId]);
const pr = prs[0];
// Verify if there are any items of EE/ME types in this PR
const [eeCount] = await pool.query("SELECT COUNT(*) as count FROM project_pr_items WHERE pr_id = ? AND system_type = 'EE'", [prId]);
const [meCount] = await pool.query("SELECT COUNT(*) as count FROM project_pr_items WHERE pr_id = ? AND system_type = 'ME'", [prId]);
const needsEEApproval = eeCount[0].count > 0;
const needsMEApproval = meCount[0].count > 0;
const eeApproved = !needsEEApproval || pr.ee_manager_approved === 1;
const meApproved = !needsMEApproval || pr.me_manager_approved === 1;
const eeRejected = needsEEApproval && pr.ee_manager_approved === -1;
const meRejected = needsMEApproval && pr.me_manager_approved === -1;
if (eeRejected || meRejected) {
await pool.query("UPDATE project_prs SET status = 'rejected' WHERE id = ?", [prId]);
} else if (eeApproved && meApproved) {
await pool.query("UPDATE project_prs SET status = 'pending_executive_approval' WHERE id = ?", [prId]);
}
await logActivity(userId, pr.project_id, 'PR_REVIEW', `Manager reviewed ${system_type} items for PR #${prId}: ${approve ? 'Approved' : 'Rejected'}`);
res.json({ message: 'Review recorded successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 7. Executive Approval & PO Conversion
app.post('/api/prs/:id/approve', requireAuth, async (req, res) => {
const prId = req.params.id;
const { approve } = req.body;
const userId = req.user.id;
const userRole = req.user.role;
if (userRole !== 'excusive' && userRole !== 'admin' && userRole !== 'superadmin') {
return res.status(403).json({ error: 'Only Executives can approve PRs' });
}
try {
const [prs] = await pool.query("SELECT * FROM project_prs WHERE id = ?", [prId]);
if (prs.length === 0) return res.status(404).json({ error: 'PR not found' });
const pr = prs[0];
if (approve) {
await pool.query(
"UPDATE project_prs SET status = 'approved', executive_approved = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
[prId]
);
// Auto-convert to POs grouped by supplier_id
const [items] = await pool.query("SELECT * FROM project_pr_items WHERE pr_id = ? AND supplier_id IS NOT NULL", [prId]);
// Group items by supplier
const supplierGroups = {};
items.forEach(item => {
if (!supplierGroups[item.supplier_id]) {
supplierGroups[item.supplier_id] = [];
}
supplierGroups[item.supplier_id].push(item);
});
for (const [supplierId, prItems] of Object.entries(supplierGroups)) {
const result = await pool.query(
"INSERT INTO project_pos (pr_id, project_id, created_by, supplier_id, status) VALUES (?, ?, ?, ?, 'pending_approval')",
[prId, pr.project_id, userId, supplierId]
);
const poId = result.insertId;
for (const prItem of prItems) {
await pool.query(
"INSERT INTO project_po_items (po_id, pr_item_id, qty, price, received_qty) VALUES (?, ?, ?, ?, 0)",
[poId, prItem.id, prItem.qty, prItem.sourced_price]
);
}
}
await logActivity(userId, pr.project_id, 'PR_APPROVE', `Executive approved PR #${prId} and generated Purchase Orders`);
} else {
await pool.query(
"UPDATE project_prs SET status = 'rejected', executive_approved = -1, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
[prId]
);
await logActivity(userId, pr.project_id, 'PR_REJECT', `Executive rejected PR #${prId}`);
}
res.json({ message: 'PR approval status updated successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 8. Get POs
app.get('/api/pos', requireAuth, async (req, res) => {
try {
const [pos] = await pool.query(
"SELECT po.*, p.name as project_name, s.name as supplier_name, u.username as creator_name FROM project_pos po JOIN projects p ON po.project_id = p.id JOIN suppliers s ON po.supplier_id = s.id LEFT JOIN users u ON po.created_by = u.id ORDER BY po.id DESC"
);
res.json(pos);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 9. Get Single PO details
app.get('/api/pos/:id', requireAuth, async (req, res) => {
try {
const poId = req.params.id;
const [pos] = await pool.query("SELECT * FROM project_pos WHERE id = ?", [poId]);
if (pos.length === 0) return res.status(404).json({ error: 'PO not found' });
const [items] = await pool.query(
"SELECT poi.*, pri.description, pri.brand, pri.model, pri.unit FROM project_po_items poi JOIN project_pr_items pri ON poi.pr_item_id = pri.id WHERE poi.po_id = ?",
[poId]
);
res.json({ po: pos[0], items });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 10. Executive approves PO
app.post('/api/pos/:id/approve', requireAuth, async (req, res) => {
const poId = req.params.id;
const { approve } = req.body;
const userId = req.user.id;
const userRole = req.user.role;
if (userRole !== 'excusive' && userRole !== 'admin' && userRole !== 'superadmin') {
return res.status(403).json({ error: 'Only Executives can approve POs' });
}
try {
const status = approve ? 'approved' : 'rejected';
await pool.query(
"UPDATE project_pos SET status = ?, executive_approved = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
[status, approve ? 1 : -1, poId]
);
await logActivity(userId, null, 'PO_APPROVE', `Executive ${approve ? 'approved' : 'rejected'} PO #${poId}`);
res.json({ message: 'PO status updated successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 11. Stock receives PO goods
app.post('/api/pos/:id/receive', requireAuth, async (req, res) => {
const poId = req.params.id;
const { items } = req.body; // Array of { po_item_id, qty, type ('consumable'/'tool_machinery'), location, min_qty, is_defective }
const userId = req.user.id;
if (!items || items.length === 0) {
return res.status(400).json({ error: 'No items to receive' });
}
try {
const [pos] = await pool.query("SELECT * FROM project_pos WHERE id = ?", [poId]);
if (pos.length === 0) return res.status(404).json({ error: 'PO not found' });
const po = pos[0];
for (const item of items) {
const [poItems] = await pool.query(
"SELECT poi.*, pri.description, pri.brand, pri.model, pri.unit FROM project_po_items poi JOIN project_pr_items pri ON poi.pr_item_id = pri.id WHERE poi.id = ?",
[item.po_item_id]
);
if (poItems.length === 0) continue;
const poItem = poItems[0];
if (item.is_defective) {
// Send to claims
await pool.query(
"INSERT INTO stock_movements (stock_item_id, po_id, type, qty, status) VALUES (?, ?, 'claim', ?, 'approved')",
[-1, poId, item.qty]
);
await logActivity(userId, po.project_id, 'STOCK_CLAIM', `Reported defective claim of ${item.qty} units for "${poItem.description}"`);
} else {
// Add/update stock level
const [existingStock] = await pool.query(
"SELECT id, qty FROM stock_items WHERE description = ? AND brand = ? AND model = ?",
[poItem.description, poItem.brand, poItem.model]
);
let stockItemId;
if (existingStock.length > 0) {
stockItemId = existingStock[0].id;
await pool.query(
"UPDATE stock_items SET qty = qty + ?, last_movement_at = CURRENT_TIMESTAMP WHERE id = ?",
[item.qty, stockItemId]
);
} else {
const insRes = await pool.query(
"INSERT INTO stock_items (description, brand, model, unit, qty, type, location, min_qty) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
[poItem.description, poItem.brand, poItem.model, poItem.unit, item.qty, item.type || 'consumable', item.location || '', item.min_qty || 0]
);
stockItemId = insRes.insertId;
}
// Record incoming movement
await pool.query(
"INSERT INTO stock_movements (stock_item_id, po_id, type, qty, status) VALUES (?, ?, 'in', ?, 'approved')",
[stockItemId, poId, item.qty]
);
// Update PO item received quantity
await pool.query(
"UPDATE project_po_items SET received_qty = received_qty + ? WHERE id = ?",
[item.qty, item.po_item_id]
);
}
}
// Check if PO is fully received
const [poItemsStatus] = await pool.query(
"SELECT SUM(qty) as total_qty, SUM(received_qty) as total_received FROM project_po_items WHERE po_id = ?",
[poId]
);
const total = poItemsStatus[0].total_qty || 0;
const received = poItemsStatus[0].total_received || 0;
const newStatus = received >= total ? 'received' : (received > 0 ? 'partially_received' : 'approved');
await pool.query("UPDATE project_pos SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?", [newStatus, poId]);
await logActivity(userId, po.project_id, 'STOCK_RECEIVE', `Received goods from PO #${poId}`);
res.json({ message: 'Goods received and stock updated successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 12. Get Current Stock levels
app.get('/api/stock', requireAuth, async (req, res) => {
try {
const [stock] = await pool.query("SELECT * FROM stock_items ORDER BY description ASC");
res.json(stock);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 13. Disburse stock items to a project
app.post('/api/stock/disburse', requireAuth, async (req, res) => {
const { stock_item_id, qty, project_id } = req.body;
const userId = req.user.id;
try {
const [stock] = await pool.query("SELECT qty, description FROM stock_items WHERE id = ?", [stock_item_id]);
if (stock.length === 0) return res.status(404).json({ error: 'Stock item not found' });
if (stock[0].qty < qty) {
return res.status(400).json({ error: 'Insufficient stock quantity available' });
}
// Create stock movement out but pending approval from project
const result = await pool.query(
"INSERT INTO stock_movements (stock_item_id, project_id, type, qty, status, approved_by) VALUES (?, ?, 'out', ?, 'pending_approval', ?)",
[stock_item_id, project_id, qty, userId]
);
await logActivity(userId, project_id, 'STOCK_DISBURSE_REQUEST', `Requested disbursement of ${qty} units of "${stock[0].description}" to Project ID ${project_id}`);
res.json({ id: result.insertId, message: 'Disbursement request created, pending approval' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 14. Approve disbursement of stock items to project
app.post('/api/stock/approve-disbursement', requireAuth, async (req, res) => {
const { movement_id, approve } = req.body;
const userId = req.user.id;
try {
const [movements] = await pool.query("SELECT * FROM stock_movements WHERE id = ?", [movement_id]);
if (movements.length === 0) return res.status(404).json({ error: 'Movement not found' });
const m = movements[0];
if (approve) {
// Verify quantity again
const [stock] = await pool.query("SELECT qty, description FROM stock_items WHERE id = ?", [m.stock_item_id]);
if (stock[0].qty < m.qty) {
return res.status(400).json({ error: 'Insufficient stock available to fulfill request' });
}
await pool.query(
"UPDATE stock_items SET qty = qty - ?, last_movement_at = CURRENT_TIMESTAMP WHERE id = ?",
[m.qty, m.stock_item_id]
);
await pool.query(
"UPDATE stock_movements SET status = 'approved', approved_by = ? WHERE id = ?",
[userId, movement_id]
);
await logActivity(userId, m.project_id, 'STOCK_DISBURSE_APPROVE', `Approved disbursement of ${m.qty} units of "${stock[0].description}"`);
} else {
await pool.query(
"UPDATE stock_movements SET status = 'rejected', approved_by = ? WHERE id = ?",
[userId, movement_id]
);
await logActivity(userId, m.project_id, 'STOCK_DISBURSE_REJECT', `Rejected disbursement of ${m.qty} units`);
}
res.json({ message: 'Disbursement request updated successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 15. Return unused stock from project
app.post('/api/stock/return', requireAuth, async (req, res) => {
const { stock_item_id, qty, project_id } = req.body;
const userId = req.user.id;
try {
await pool.query(
"UPDATE stock_items SET qty = qty + ?, last_movement_at = CURRENT_TIMESTAMP WHERE id = ?",
[qty, stock_item_id]
);
await pool.query(
"INSERT INTO stock_movements (stock_item_id, project_id, type, qty, status) VALUES (?, ?, 'return', ?, 'approved')",
[stock_item_id, project_id, qty]
);
const [stock] = await pool.query("SELECT description FROM stock_items WHERE id = ?", [stock_item_id]);
await logActivity(userId, project_id, 'STOCK_RETURN', `Returned ${qty} units of "${stock[0].description}" to stock`);
res.json({ message: 'Items returned to stock successfully' });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 16. Report damage or loss
app.post('/api/stock/report', requireAuth, async (req, res) => {
const { stock_item_id, qty, type } = req.body; // type: 'damage' or 'loss'
const userId = req.user.id;
if (type !== 'damage' && type !== 'loss') {
return res.status(400).json({ error: 'Invalid report type' });
}
try {
const [stock] = await pool.query("SELECT qty, description FROM stock_items WHERE id = ?", [stock_item_id]);
if (stock.length === 0) return res.status(404).json({ error: 'Stock item not found' });
if (stock[0].qty < qty) {
return res.status(400).json({ error: 'Reported quantity exceeds quantity currently in stock' });
}
await pool.query(
"UPDATE stock_items SET qty = qty - ?, last_movement_at = CURRENT_TIMESTAMP WHERE id = ?",
[qty, stock_item_id]
);
await pool.query(
"INSERT INTO stock_movements (stock_item_id, type, qty, status) VALUES (?, ?, ?, 'approved')",
[stock_item_id, type, qty]
);
await logActivity(userId, null, 'STOCK_LOSS_REPORT', `Reported ${qty} units of "${stock[0].description}" as ${type}`);
res.json({ message: `Reported items as ${type} successfully` });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 17. Stock Alerts (Safety stock + Dead Stock Alerts)
app.get('/api/stock/alerts', requireAuth, async (req, res) => {
try {
// Safety stock alerts: qty < min_qty
const [safetyAlerts] = await pool.query(
"SELECT * FROM stock_items WHERE qty < min_qty"
);
// Dead stock alerts: last_movement_at > 3 months (no movement > 3 months) or sitting > 6 months
const [deadStockAlerts] = await pool.query(`
SELECT *,
CASE
WHEN last_movement_at < datetime('now', '-6 month') THEN 'inactive_6m'
WHEN last_movement_at < datetime('now', '-3 month') THEN 'inactive_3m'
ELSE 'active'
END as alert_type
FROM stock_items
WHERE last_movement_at < datetime('now', '-3 month')
`);
res.json({ safety: safetyAlerts, dead: deadStockAlerts });
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
// 18. Get Stock Movements list
app.get('/api/stock/movements', requireAuth, async (req, res) => {
try {
const status = req.query.status;
let query = "SELECT sm.*, si.description, si.unit, p.name as project_name, u.username as requester_name FROM stock_movements sm JOIN stock_items si ON sm.stock_item_id = si.id LEFT JOIN projects p ON sm.project_id = p.id LEFT JOIN users u ON sm.approved_by = u.id";
const params = [];
if (status) {
query += " WHERE sm.status = ?";
params.push(status);
}
query += " ORDER BY sm.id DESC";
const [movements] = await pool.query(query, params);
res.json(movements);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Internal Server Error' });
}
});
};