Danial HB
557 lines
22 KiB
JavaScript
557 lines
22 KiB
JavaScript
const express = require('express');
|
|
const mysql = require('mysql2');
|
|
const cors = require('cors');
|
|
const path = require('path');
|
|
const bcrypt = require('bcrypt');
|
|
const jwt = require('jsonwebtoken');
|
|
require('dotenv').config();
|
|
|
|
const JWT_SECRET = process.env.JWT_SECRET || 'super-secret-key-donations-webgis';
|
|
|
|
const app = express();
|
|
app.use(cors());
|
|
app.use(express.json());
|
|
app.use(express.static(path.join(__dirname, 'public')));
|
|
|
|
// Database connection
|
|
let db;
|
|
let migrationsRun = false;
|
|
function handleDisconnect() {
|
|
const connectionString = process.env.DATABASE_URL || process.env.MYSQL_URL || process.env.MYSQL_PRIVATE_URL;
|
|
db = connectionString
|
|
? mysql.createConnection(connectionString)
|
|
: mysql.createConnection({
|
|
host: process.env.DB_HOST || 'localhost',
|
|
user: process.env.DB_USER || 'root',
|
|
password: process.env.DB_PASSWORD || '',
|
|
database: process.env.DB_NAME || 'webgis_donations',
|
|
port: process.env.DB_PORT || 3306
|
|
});
|
|
|
|
db.connect(err => {
|
|
if (err) {
|
|
console.error('Database connection failed:', err);
|
|
setTimeout(handleDisconnect, 2000);
|
|
return;
|
|
}
|
|
console.log('Connected to MySQL database.');
|
|
|
|
if (!migrationsRun) {
|
|
migrationsRun = true;
|
|
db.query("SHOW COLUMNS FROM distribution_history LIKE 'verification_status'", (err, results) => {
|
|
if (err || results.length > 0) return;
|
|
console.log('Applying distribution_history schema migration...');
|
|
const alterSql = `
|
|
ALTER TABLE distribution_history
|
|
ADD COLUMN verification_status enum('pending','verified','rejected') NOT NULL DEFAULT 'pending',
|
|
ADD COLUMN verified_by int DEFAULT NULL,
|
|
ADD COLUMN verified_at datetime DEFAULT NULL,
|
|
ADD KEY verified_by (verified_by),
|
|
ADD CONSTRAINT distribution_history_ibfk_4 FOREIGN KEY (verified_by) REFERENCES users (id) ON DELETE SET NULL
|
|
`;
|
|
db.query(alterSql, (alterErr) => {
|
|
if (alterErr) console.error('Failed to migrate distribution_history schema:', alterErr);
|
|
else console.log('distribution_history schema migrated successfully.');
|
|
});
|
|
});
|
|
// Ensure religious_places has place_type column
|
|
db.query("SHOW COLUMNS FROM religious_places LIKE 'place_type'", (err2, res2) => {
|
|
if (err2 || res2.length > 0) return;
|
|
console.log('Applying religious_places place_type migration...');
|
|
const alterPlaces = `
|
|
ALTER TABLE religious_places
|
|
ADD COLUMN place_type enum('mosque','church_catholic','church_protestant','pura','vihara','kelenteng') NOT NULL DEFAULT 'mosque'
|
|
`;
|
|
db.query(alterPlaces, (aErr) => {
|
|
if (aErr) console.error('Failed to migrate religious_places schema:', aErr);
|
|
else console.log('religious_places schema migrated successfully.');
|
|
});
|
|
});
|
|
}
|
|
});
|
|
|
|
db.on('error', err => {
|
|
console.error('Database error:', err);
|
|
if (err.code === 'PROTOCOL_CONNECTION_LOST') {
|
|
handleDisconnect();
|
|
} else {
|
|
throw err;
|
|
}
|
|
});
|
|
}
|
|
handleDisconnect();
|
|
|
|
// --- MIDDLEWARES ---
|
|
|
|
// Middleware: Authenticate JWT
|
|
const authenticateJWT = (req, res, next) => {
|
|
const authHeader = req.headers.authorization;
|
|
if (authHeader) {
|
|
const token = authHeader.split(' ')[1];
|
|
jwt.verify(token, JWT_SECRET, (err, user) => {
|
|
if (err) return res.sendStatus(403);
|
|
req.user = user;
|
|
next();
|
|
});
|
|
} else {
|
|
res.sendStatus(401);
|
|
}
|
|
};
|
|
|
|
// Middleware: Authorize Roles
|
|
const authorizeRoles = (...roles) => {
|
|
return (req, res, next) => {
|
|
if (!req.user || !roles.includes(req.user.role)) {
|
|
return res.status(403).json({ message: "Forbidden: insufficient permissions." });
|
|
}
|
|
next();
|
|
};
|
|
};
|
|
|
|
// Middleware: Check Ownership for Surveyors
|
|
const checkOwnership = (tableName) => {
|
|
return (req, res, next) => {
|
|
if (req.user.role === 'admin') return next(); // Admins can manage everything
|
|
|
|
let sql = `SELECT added_by FROM ${tableName} WHERE id = ?`;
|
|
if (tableName === 'households') {
|
|
sql = `SELECT surveyor_id AS added_by FROM poverty_assessments WHERE household_id = ? LIMIT 1`;
|
|
}
|
|
|
|
// Ensure surveyors only manage what they added
|
|
db.query(sql, [req.params.id], (err, results) => {
|
|
if (err) return res.status(500).send(err);
|
|
if (results.length === 0) return res.status(404).send('Not Found');
|
|
if (results[0].added_by !== req.user.id) return res.status(403).json({ message: "Forbidden: You did not add this entry." });
|
|
next();
|
|
});
|
|
};
|
|
};
|
|
|
|
// --- AUTHENTICATION ROUTES ---
|
|
|
|
// Initialize default admin if no admins exist
|
|
app.post('/api/auth/init', async (req, res) => {
|
|
try {
|
|
db.query("SELECT COUNT(*) as count FROM users WHERE role = 'admin'", async (err, results) => {
|
|
if (err) return res.status(500).send(err);
|
|
if (results[0].count > 0) return res.status(403).json({ message: 'Admin already initialized.' });
|
|
|
|
const hashedPassword = await bcrypt.hash('admin123', 10);
|
|
db.query("INSERT INTO users (username, email, password_hash, role) VALUES (?, ?, ?, 'admin')",
|
|
['admin', 'admin@webgis.com', hashedPassword], (err) => {
|
|
if (err) return res.status(500).send(err);
|
|
res.json({ message: 'Default admin initialized. (username: admin, email: admin@webgis.com, pass: admin123)' });
|
|
});
|
|
});
|
|
} catch (err) {
|
|
res.status(500).send(err);
|
|
}
|
|
});
|
|
|
|
// Register new users (Admins only)
|
|
app.post('/api/auth/register', authenticateJWT, authorizeRoles('admin'), async (req, res) => {
|
|
const { username, email, password, role } = req.body;
|
|
try {
|
|
const hashedPassword = await bcrypt.hash(password, 10);
|
|
db.query('INSERT INTO users (username, email, password_hash, role) VALUES (?, ?, ?, ?)',
|
|
[username, email, hashedPassword, role || 'surveyor'], (err, result) => {
|
|
if (err) {
|
|
if (err.code === 'ER_DUP_ENTRY') return res.status(400).json({ message: 'Username or email already exists' });
|
|
return res.status(500).send(err);
|
|
}
|
|
res.status(201).json({ message: 'User created successfully', userId: result.insertId });
|
|
});
|
|
} catch (err) {
|
|
res.status(500).send(err);
|
|
}
|
|
});
|
|
|
|
// Login
|
|
app.post('/api/auth/login', (req, res) => {
|
|
const { email, password } = req.body;
|
|
db.query('SELECT * FROM users WHERE email = ?', [email], async (err, results) => {
|
|
if (err) return res.status(500).send(err);
|
|
if (results.length === 0) return res.status(401).json({ message: 'Invalid email or password' });
|
|
|
|
const user = results[0];
|
|
const validPassword = await bcrypt.compare(password, user.password_hash);
|
|
if (!validPassword) return res.status(401).json({ message: 'Invalid email or password' });
|
|
|
|
const token = jwt.sign({ id: user.id, role: user.role, username: user.username }, JWT_SECRET, { expiresIn: '24h' });
|
|
res.json({ message: 'Login successful', token, role: user.role, username: user.username });
|
|
});
|
|
});
|
|
|
|
// GET endpoints
|
|
app.get('/api/places', (req, res) => {
|
|
db.query('SELECT * FROM religious_places', (err, results) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.json(results);
|
|
});
|
|
});
|
|
|
|
app.get('/api/houses', (req, res) => {
|
|
const query = `
|
|
SELECT
|
|
h.id,
|
|
h.kk_number,
|
|
h.head_nik,
|
|
h.head_name,
|
|
h.head_name AS name,
|
|
h.resident_count,
|
|
h.lat,
|
|
h.lng,
|
|
h.linked_place_id,
|
|
pa.id AS assessment_id,
|
|
pa.electricity_va,
|
|
pa.floor_material,
|
|
pa.wall_material,
|
|
pa.drinking_water_source,
|
|
pa.photo_evidence_url,
|
|
pa.verification_status,
|
|
pa.surveyor_id,
|
|
pa.verified_by
|
|
FROM households h
|
|
INNER JOIN poverty_assessments pa ON h.id = pa.household_id
|
|
`;
|
|
db.query(query, (err, results) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.json(results);
|
|
});
|
|
});
|
|
|
|
// POST endpoints (Creation)
|
|
app.post('/api/places', authenticateJWT, authorizeRoles('admin', 'surveyor'), (req, res) => {
|
|
const { name, lat, lng, radius, place_type } = req.body;
|
|
db.query('INSERT INTO religious_places (name, lat, lng, radius, place_type, added_by) VALUES (?, ?, ?, ?, ?, ?)',
|
|
[name, lat, lng, radius, place_type || 'mosque', req.user.id], (err, result) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.json({ id: result.insertId, name, lat, lng, radius, place_type: place_type || 'mosque', added_by: req.user.id });
|
|
});
|
|
});
|
|
|
|
app.post('/api/houses', authenticateJWT, authorizeRoles('admin', 'surveyor'), (req, res) => {
|
|
const {
|
|
kk_number,
|
|
head_nik,
|
|
head_name,
|
|
resident_count,
|
|
lat,
|
|
lng,
|
|
linked_place_id,
|
|
electricity_va,
|
|
floor_material,
|
|
wall_material,
|
|
drinking_water_source,
|
|
photo_evidence_url
|
|
} = req.body;
|
|
|
|
db.beginTransaction(err => {
|
|
if (err) return res.status(500).send(err);
|
|
|
|
const insertHousehold = `
|
|
INSERT INTO households (kk_number, head_nik, head_name, resident_count, lat, lng, linked_place_id)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)
|
|
`;
|
|
db.query(insertHousehold, [kk_number, head_nik, head_name, resident_count, lat, lng, linked_place_id], (err, result) => {
|
|
if (err) {
|
|
return db.rollback(() => {
|
|
res.status(500).send(err);
|
|
});
|
|
}
|
|
|
|
const householdId = result.insertId;
|
|
const insertAssessment = `
|
|
INSERT INTO poverty_assessments (household_id, surveyor_id, electricity_va, floor_material, wall_material, drinking_water_source, photo_evidence_url, verification_status)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, 'pending')
|
|
`;
|
|
db.query(insertAssessment, [householdId, req.user.id, electricity_va, floor_material, wall_material, drinking_water_source, photo_evidence_url || null], (err, resultAssess) => {
|
|
if (err) {
|
|
return db.rollback(() => {
|
|
res.status(500).send(err);
|
|
});
|
|
}
|
|
|
|
db.commit(err => {
|
|
if (err) {
|
|
return db.rollback(() => {
|
|
res.status(500).send(err);
|
|
});
|
|
}
|
|
res.status(201).json({
|
|
id: householdId,
|
|
kk_number,
|
|
head_nik,
|
|
head_name,
|
|
name: head_name, // Aliased for client-side backward compatibility
|
|
resident_count: parseInt(resident_count),
|
|
lat: parseFloat(lat),
|
|
lng: parseFloat(lng),
|
|
linked_place_id,
|
|
assessment_id: resultAssess.insertId,
|
|
electricity_va,
|
|
floor_material,
|
|
wall_material,
|
|
drinking_water_source,
|
|
photo_evidence_url: photo_evidence_url || null,
|
|
verification_status: 'pending',
|
|
surveyor_id: req.user.id,
|
|
verified_by: null
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
|
|
// PUT endpoints (Updating location via Drag)
|
|
app.put('/api/places/:id', authenticateJWT, authorizeRoles('admin', 'surveyor'), checkOwnership('religious_places'), (req, res) => {
|
|
const { lat, lng } = req.body;
|
|
db.query('UPDATE religious_places SET lat=?, lng=? WHERE id=?',
|
|
[lat, lng, req.params.id], (err) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.sendStatus(200);
|
|
});
|
|
});
|
|
|
|
app.put('/api/houses/:id', authenticateJWT, authorizeRoles('admin', 'surveyor'), checkOwnership('households'), (req, res) => {
|
|
const { lat, lng, linked_place_id } = req.body;
|
|
db.query('UPDATE households SET lat=?, lng=?, linked_place_id=? WHERE id=?',
|
|
[lat, lng, linked_place_id, req.params.id], (err) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.sendStatus(200);
|
|
});
|
|
});
|
|
|
|
// PUT endpoints (Updating Attributes)
|
|
app.put('/api/places/attr/:id', authenticateJWT, authorizeRoles('admin', 'surveyor'), checkOwnership('religious_places'), (req, res) => {
|
|
const { name, radius, place_type } = req.body;
|
|
db.query('UPDATE religious_places SET name=?, radius=?, place_type=? WHERE id=?',
|
|
[name, radius, place_type || 'mosque', req.params.id], (err) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.sendStatus(200);
|
|
});
|
|
});
|
|
|
|
app.put('/api/houses/attr/:id', authenticateJWT, authorizeRoles('admin', 'surveyor'), checkOwnership('households'), (req, res) => {
|
|
const {
|
|
head_name,
|
|
kk_number,
|
|
head_nik,
|
|
resident_count,
|
|
electricity_va,
|
|
floor_material,
|
|
wall_material,
|
|
drinking_water_source
|
|
} = req.body;
|
|
|
|
db.beginTransaction(err => {
|
|
if (err) return res.status(500).send(err);
|
|
|
|
const updateHousehold = 'UPDATE households SET head_name=?, kk_number=?, head_nik=?, resident_count=? WHERE id=?';
|
|
db.query(updateHousehold, [head_name, kk_number, head_nik, resident_count, req.params.id], (err) => {
|
|
if (err) {
|
|
return db.rollback(() => {
|
|
res.status(500).send(err);
|
|
});
|
|
}
|
|
|
|
const updateAssessment = 'UPDATE poverty_assessments SET electricity_va=?, floor_material=?, wall_material=?, drinking_water_source=? WHERE household_id=?';
|
|
db.query(updateAssessment, [electricity_va, floor_material, wall_material, drinking_water_source, req.params.id], (err) => {
|
|
if (err) {
|
|
return db.rollback(() => {
|
|
res.status(500).send(err);
|
|
});
|
|
}
|
|
|
|
db.commit(err => {
|
|
if (err) {
|
|
return db.rollback(() => {
|
|
res.status(500).send(err);
|
|
});
|
|
}
|
|
res.sendStatus(200);
|
|
});
|
|
});
|
|
});
|
|
});
|
|
});
|
|
|
|
app.put('/api/houses/verify/:id', authenticateJWT, authorizeRoles('admin'), (req, res) => {
|
|
const verifySql = `
|
|
UPDATE poverty_assessments
|
|
SET verification_status = 'verified', verified_by = ?
|
|
WHERE household_id = ?
|
|
`;
|
|
db.query(verifySql, [req.user.id, req.params.id], (err, result) => {
|
|
if (err) return res.status(500).json({ message: 'Database error', error: err.message });
|
|
if (result.affectedRows === 0) {
|
|
return res.status(404).json({ message: 'Household assessment not found or already verified.' });
|
|
}
|
|
res.json({ message: 'Household verified successfully.' });
|
|
});
|
|
});
|
|
|
|
// Admin: list households pending verification
|
|
app.get('/api/houses/pending', authenticateJWT, authorizeRoles('admin'), (req, res) => {
|
|
const sql = `
|
|
SELECT pa.id AS assessment_id, h.id AS household_id, h.head_name, h.kk_number, h.head_nik, h.resident_count,
|
|
h.lat, h.lng, h.linked_place_id, pa.electricity_va, pa.floor_material, pa.wall_material, pa.drinking_water_source, pa.photo_evidence_url, pa.verification_status, pa.surveyor_id, u.username AS surveyor_name, rp.name AS place_name
|
|
FROM poverty_assessments pa
|
|
JOIN households h ON h.id = pa.household_id
|
|
LEFT JOIN users u ON u.id = pa.surveyor_id
|
|
LEFT JOIN religious_places rp ON rp.id = h.linked_place_id
|
|
WHERE pa.verification_status = 'pending'
|
|
ORDER BY pa.id DESC
|
|
`;
|
|
db.query(sql, (err, results) => {
|
|
if (err) return res.status(500).json({ message: 'Database error', error: err.message });
|
|
res.json(results);
|
|
});
|
|
});
|
|
|
|
// --- SEMBAKO DISTRIBUTION ROUTES ---
|
|
|
|
/**
|
|
* GET /api/analytics/sembako-demand
|
|
* Returns a list of religious centers with the count of 'verified' households
|
|
* that match BPS poverty criteria and have NEVER received sembako aid.
|
|
* Protected: admin or surveyor must be logged in.
|
|
*/
|
|
app.get('/api/analytics/sembako-demand', authenticateJWT, (req, res) => {
|
|
const sql = `
|
|
SELECT
|
|
rp.id AS place_id,
|
|
rp.name AS place_name,
|
|
rp.place_type AS place_type,
|
|
COUNT(DISTINCT h.id) AS households_needing_aid,
|
|
SUM(h.resident_count) AS total_residents_needing_aid
|
|
FROM religious_places rp
|
|
JOIN households h
|
|
ON h.linked_place_id = rp.id
|
|
JOIN poverty_assessments pa
|
|
ON pa.household_id = h.id
|
|
AND pa.verification_status = 'verified'
|
|
AND (
|
|
pa.electricity_va IN ('none', '450')
|
|
OR pa.floor_material IN ('dirt', 'bamboo')
|
|
)
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM distribution_history dh
|
|
WHERE dh.house_id = h.id
|
|
AND dh.aid_category = 'sembako'
|
|
AND dh.verification_status = 'verified'
|
|
)
|
|
GROUP BY rp.id, rp.name
|
|
ORDER BY households_needing_aid DESC
|
|
`;
|
|
db.query(sql, (err, results) => {
|
|
if (err) return res.status(500).json({ message: 'Database error', error: err.message });
|
|
res.json(results);
|
|
});
|
|
});
|
|
|
|
/**
|
|
* POST /api/distribution/log
|
|
* Logs a completed sembako handover into distribution_history.
|
|
* Body: { household_id, place_id, amount_value, notes }
|
|
* Protected: admin or surveyor only.
|
|
*/
|
|
app.post('/api/distribution/log', authenticateJWT, authorizeRoles('admin', 'surveyor'), (req, res) => {
|
|
const { household_id, place_id, amount_value, notes } = req.body;
|
|
|
|
if (!household_id) {
|
|
return res.status(400).json({ message: 'household_id is required.' });
|
|
}
|
|
|
|
const sql = `
|
|
INSERT INTO distribution_history
|
|
(house_id, place_id, distributed_by, aid_category, amount_value, notes, verification_status)
|
|
VALUES (?, ?, ?, 'sembako', ?, ?, 'pending')
|
|
`;
|
|
db.query(
|
|
sql,
|
|
[
|
|
household_id,
|
|
place_id || null,
|
|
req.user.id,
|
|
amount_value || 1,
|
|
notes || null
|
|
],
|
|
(err, result) => {
|
|
if (err) return res.status(500).json({ message: 'Failed to log distribution.', error: err.message });
|
|
res.status(201).json({
|
|
message: 'Sembako distribution logged successfully.',
|
|
distribution_id: result.insertId
|
|
});
|
|
}
|
|
);
|
|
});
|
|
|
|
app.get('/api/distribution/pending', authenticateJWT, authorizeRoles('admin'), (req, res) => {
|
|
const sql = `
|
|
SELECT
|
|
dh.id,
|
|
dh.house_id,
|
|
dh.place_id,
|
|
dh.distributed_by,
|
|
dh.amount_value,
|
|
dh.notes,
|
|
dh.verification_status,
|
|
dh.verified_by,
|
|
dh.verified_at,
|
|
h.head_name,
|
|
h.kk_number,
|
|
h.head_nik,
|
|
h.resident_count,
|
|
r.name AS place_name,
|
|
u.username AS distributed_by_name
|
|
FROM distribution_history dh
|
|
LEFT JOIN households h ON h.id = dh.house_id
|
|
LEFT JOIN religious_places r ON r.id = dh.place_id
|
|
LEFT JOIN users u ON u.id = dh.distributed_by
|
|
WHERE dh.aid_category = 'sembako'
|
|
AND dh.verification_status = 'pending'
|
|
ORDER BY dh.id DESC
|
|
`;
|
|
db.query(sql, (err, results) => {
|
|
if (err) return res.status(500).json({ message: 'Database error', error: err.message });
|
|
res.json(results);
|
|
});
|
|
});
|
|
|
|
app.put('/api/distribution/verify/:id', authenticateJWT, authorizeRoles('admin'), (req, res) => {
|
|
const sql = `
|
|
UPDATE distribution_history
|
|
SET verification_status = 'verified', verified_by = ?, verified_at = NOW()
|
|
WHERE id = ?
|
|
AND aid_category = 'sembako'
|
|
AND verification_status = 'pending'
|
|
`;
|
|
db.query(sql, [req.user.id, req.params.id], (err, result) => {
|
|
if (err) return res.status(500).json({ message: 'Database error', error: err.message });
|
|
if (result.affectedRows === 0) {
|
|
return res.status(404).json({ message: 'Pending handover not found or already verified.' });
|
|
}
|
|
res.json({ message: 'Handover verified successfully.' });
|
|
});
|
|
});
|
|
|
|
// DELETE endpoints
|
|
app.delete('/api/places/:id', authenticateJWT, authorizeRoles('admin'), (req, res) => {
|
|
db.query('DELETE FROM religious_places WHERE id=?', [req.params.id], (err) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.sendStatus(200);
|
|
});
|
|
});
|
|
|
|
app.delete('/api/houses/:id', authenticateJWT, authorizeRoles('admin'), (req, res) => {
|
|
db.query('DELETE FROM households WHERE id=?', [req.params.id], (err) => {
|
|
if (err) res.status(500).send(err);
|
|
else res.sendStatus(200);
|
|
});
|
|
});
|
|
|
|
const PORT = process.env.PORT || 3000;
|
|
app.listen(PORT, () => console.log(`Server running on http://localhost:${PORT}`)); |