426 lines
33 KiB
JavaScript
426 lines
33 KiB
JavaScript
const express = require('express');
|
|
const path = require('node:path');
|
|
const crypto = require('node:crypto');
|
|
const { Pool } = require('pg');
|
|
|
|
const app = express();
|
|
const port = Number(process.env.PORT || 3000);
|
|
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
|
|
const sessions = new Map();
|
|
|
|
function hashPassword(password, salt = crypto.randomBytes(16).toString('hex')) {
|
|
return new Promise((resolve, reject) => crypto.scrypt(String(password), salt, 64, (error, derivedKey) => error ? reject(error) : resolve(`${salt}:${derivedKey.toString('hex')}`)));
|
|
}
|
|
|
|
function verifyPassword(password, storedHash) {
|
|
return new Promise((resolve, reject) => {
|
|
const [salt, hash] = String(storedHash || '').split(':');
|
|
if (!salt || !hash) return resolve(false);
|
|
crypto.scrypt(String(password), salt, 64, (error, derivedKey) => {
|
|
if (error) return reject(error);
|
|
const expected = Buffer.from(hash, 'hex');
|
|
resolve(expected.length === derivedKey.length && crypto.timingSafeEqual(expected, derivedKey));
|
|
});
|
|
});
|
|
}
|
|
|
|
function getSessionUser(request) {
|
|
const token = String(request.headers.cookie || '').split(';').map((part) => part.trim()).find((part) => part.startsWith('car_service_session='))?.split('=')[1];
|
|
return token ? sessions.get(token) : null;
|
|
}
|
|
|
|
function requirePermission(permission) {
|
|
return (request, response, next) => {
|
|
const user = getSessionUser(request);
|
|
if (!user) return response.status(401).json({ error: 'Sign in to modify workshop data.' });
|
|
if (permission === 'delete' && user.role !== 'admin') return response.status(403).json({ error: 'Only administrators can delete data.' });
|
|
if (permission === 'admin' && user.role !== 'admin') return response.status(403).json({ error: 'Only administrators can manage user accounts or restore backups.' });
|
|
if (permission === 'add' && user.can_add !== true && !['admin', 'technician', 'service_advisor'].includes(user.role)) return response.status(403).json({ error: 'Your account does not have add or modify rights.' });
|
|
next();
|
|
};
|
|
}
|
|
|
|
function normalizeItems(items) {
|
|
if (!Array.isArray(items)) return [];
|
|
return items.map((item) => {
|
|
const itemType = item.itemType === 'labor' ? 'labor' : 'part';
|
|
const description = String(item.description || '').trim();
|
|
const quantity = Number(item.quantity || 1);
|
|
const unitPrice = Number(item.unitPrice || 0);
|
|
const laborTimeMinutes = itemType === 'labor' ? Math.round(Number(item.usedTimeHours || 0) * 60) : null;
|
|
if (!description || !Number.isFinite(quantity) || quantity <= 0 || !Number.isFinite(unitPrice) || unitPrice < 0 || (itemType === 'labor' && laborTimeMinutes <= 0)) throw new Error('Each service item needs a description, valid price, and valid quantity or labor time.');
|
|
return { itemType, description, partNumber: itemType === 'part' ? String(item.partNumber || '').trim() || null : null, laborTimeMinutes, quantity, unitPrice };
|
|
});
|
|
}
|
|
|
|
async function insertItems(client, serviceOrderId, items) {
|
|
for (const item of items) await client.query(`INSERT INTO service_order_items (service_order_id, item_type, description, part_number, labor_time_minutes, quantity, unit_price) VALUES ($1, $2, $3, $4, $5, $6, $7)`, [serviceOrderId, item.itemType, item.description, item.partNumber, item.laborTimeMinutes, item.quantity, item.unitPrice]);
|
|
}
|
|
|
|
app.use(express.json());
|
|
app.use(express.static(path.join(__dirname, 'public')));
|
|
|
|
app.get('/api/auth/me', (request, response) => response.json({ user: getSessionUser(request) }));
|
|
app.post('/api/auth/login', async (request, response) => {
|
|
const username = String(request.body.username || '').trim();
|
|
const password = String(request.body.password || '');
|
|
if (!username || !password) return response.status(400).json({ error: 'Username and password are required.' });
|
|
try {
|
|
const result = await pool.query('SELECT user_id, username, display_name, email, role, can_read, can_add, can_delete, password_hash FROM app_users WHERE LOWER(username) = LOWER($1)', [username]);
|
|
if (!result.rowCount || !(await verifyPassword(password, result.rows[0].password_hash))) return response.status(401).json({ error: 'Invalid username or password.' });
|
|
const user = result.rows[0]; delete user.password_hash;
|
|
const token = crypto.randomBytes(32).toString('hex'); sessions.set(token, user);
|
|
response.setHeader('Set-Cookie', `car_service_session=${token}; HttpOnly; SameSite=Lax; Path=/; Max-Age=28800`);
|
|
response.json({ user });
|
|
} catch (error) { response.status(500).json({ error: 'Unable to sign in.' }); }
|
|
});
|
|
app.post('/api/auth/logout', (request, response) => {
|
|
const token = String(request.headers.cookie || '').split(';').map((part) => part.trim()).find((part) => part.startsWith('car_service_session='))?.split('=')[1];
|
|
if (token) sessions.delete(token);
|
|
response.setHeader('Set-Cookie', 'car_service_session=; HttpOnly; SameSite=Lax; Path=/; Max-Age=0'); response.status(204).end();
|
|
});
|
|
|
|
app.use('/api', (request, response, next) => {
|
|
if (request.path.startsWith('/auth/') || request.method === 'GET' || request.method === 'HEAD' || request.method === 'OPTIONS') return next();
|
|
if (request.path === '/data/import') return requirePermission('admin')(request, response, next);
|
|
if (request.path.startsWith('/users')) return requirePermission('admin')(request, response, next);
|
|
if (request.method === 'DELETE') return requirePermission('delete')(request, response, next);
|
|
return requirePermission('add')(request, response, next);
|
|
});
|
|
|
|
const dataTables = [
|
|
{ name: 'customers', columns: ['customer_id', 'first_name', 'last_name', 'email', 'phone', 'created_at'] },
|
|
{ name: 'employees', columns: ['employee_id', 'first_name', 'last_name', 'role', 'email', 'active'] },
|
|
{ name: 'vehicles', columns: ['vehicle_id', 'customer_id', 'registration_number', 'make', 'model', 'model_year', 'engine', 'transmission', 'fuel_type', 'color', 'vin', 'mileage', 'created_at'] },
|
|
{ name: 'appointments', columns: ['appointment_id', 'vehicle_id', 'employee_id', 'scheduled_at', 'reason', 'status', 'created_at'] },
|
|
{ name: 'service_orders', columns: ['service_order_id', 'vehicle_id', 'assigned_employee_id', 'opened_at', 'closed_at', 'complaint', 'service_date', 'diagnosis', 'status'] },
|
|
{ name: 'service_order_items', columns: ['item_id', 'service_order_id', 'item_type', 'description', 'part_number', 'labor_time_minutes', 'quantity', 'unit_price', 'created_at'] },
|
|
{ name: 'invoices', columns: ['invoice_id', 'service_order_id', 'issued_at', 'due_at', 'status', 'tax_rate'] },
|
|
{ name: 'app_users', columns: ['user_id', 'username', 'display_name', 'email', 'role', 'can_read', 'can_add', 'can_delete', 'created_at'] }
|
|
];
|
|
|
|
app.get('/api/data/export', async (_request, response) => {
|
|
try {
|
|
const tables = {};
|
|
for (const table of dataTables) {
|
|
const result = await pool.query(`SELECT ${table.columns.join(', ')} FROM ${table.name} ORDER BY 1`);
|
|
tables[table.name] = result.rows;
|
|
}
|
|
response.json({ format: 'car-service-database', version: 1, exportedAt: new Date().toISOString(), tables });
|
|
} catch (error) { response.status(500).json({ error: 'Unable to export database data.' }); }
|
|
});
|
|
|
|
app.post('/api/data/import', async (request, response) => {
|
|
const snapshot = request.body;
|
|
if (!snapshot || snapshot.format !== 'car-service-database' || snapshot.version !== 1 || !snapshot.tables || typeof snapshot.tables !== 'object') return response.status(400).json({ error: 'Invalid backup file.' });
|
|
for (const table of dataTables) if (!Array.isArray(snapshot.tables[table.name])) return response.status(400).json({ error: `Backup is missing the ${table.name} table.` });
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
await client.query('TRUNCATE TABLE invoices, service_order_items, service_orders, appointments, vehicles, employees, customers, app_users RESTART IDENTITY CASCADE');
|
|
for (const table of dataTables) {
|
|
for (const row of snapshot.tables[table.name]) {
|
|
const values = table.columns.map((column) => row[column] === undefined ? null : row[column]);
|
|
const placeholders = values.map((_value, index) => `$${index + 1}`).join(', ');
|
|
await client.query(`INSERT INTO ${table.name} (${table.columns.join(', ')}) VALUES (${placeholders})`, values);
|
|
}
|
|
}
|
|
await client.query('COMMIT');
|
|
response.json({ imported: dataTables.reduce((total, table) => total + snapshot.tables[table.name].length, 0) });
|
|
} catch (error) {
|
|
await client.query('ROLLBACK');
|
|
response.status(400).json({ error: 'Import failed. No existing data was changed.' });
|
|
} finally { client.release(); }
|
|
});
|
|
|
|
app.get('/api/summary', async (_request, response) => {
|
|
try {
|
|
const result = await pool.query(`SELECT (SELECT COUNT(*)::int FROM customers) AS customers, (SELECT COUNT(*)::int FROM vehicles) AS vehicles, (SELECT COUNT(*)::int FROM service_orders WHERE status NOT IN ('closed', 'cancelled')) AS active_orders, (SELECT COUNT(*)::int FROM appointments WHERE scheduled_at >= CURRENT_DATE AND scheduled_at < CURRENT_DATE + INTERVAL '1 day' AND status NOT IN ('cancelled', 'no_show')) AS today_appointments`);
|
|
response.json(result.rows[0]);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to load dashboard summary.' }); }
|
|
});
|
|
|
|
app.get('/api/appointments', async (_request, response) => {
|
|
try {
|
|
const result = await pool.query(`SELECT a.appointment_id, a.scheduled_at, a.reason, a.status, c.first_name || ' ' || c.last_name AS customer_name, v.registration_number, v.make, v.model FROM appointments a JOIN vehicles v ON v.vehicle_id = a.vehicle_id JOIN customers c ON c.customer_id = v.customer_id WHERE a.scheduled_at >= CURRENT_DATE AND a.status NOT IN ('cancelled', 'no_show') ORDER BY a.scheduled_at LIMIT 12`);
|
|
response.json(result.rows);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to load appointments.' }); }
|
|
});
|
|
|
|
app.get('/api/orders', async (_request, response) => {
|
|
try {
|
|
const result = await pool.query(`SELECT so.service_order_id, so.status, so.complaint, so.opened_at, c.first_name || ' ' || c.last_name AS customer_name, v.registration_number, v.make, v.model, COALESCE(e.first_name || ' ' || e.last_name, 'Unassigned') AS technician FROM service_orders so JOIN vehicles v ON v.vehicle_id = so.vehicle_id JOIN customers c ON c.customer_id = v.customer_id LEFT JOIN employees e ON e.employee_id = so.assigned_employee_id WHERE so.status NOT IN ('closed', 'cancelled') ORDER BY so.opened_at DESC LIMIT 12`);
|
|
response.json(result.rows);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to load service orders.' }); }
|
|
});
|
|
|
|
app.get('/api/service-records', async (_request, response) => {
|
|
try {
|
|
const result = await pool.query(`
|
|
SELECT so.service_order_id, so.status, so.complaint, so.opened_at, so.service_date,
|
|
v.vehicle_id, btrim(v.vin) AS vin, v.registration_number AS license_plate, v.make, v.model, v.model_year, v.engine, v.transmission, v.fuel_type, v.color, v.mileage,
|
|
c.first_name || ' ' || c.last_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
|
|
COALESCE((SELECT SUM(CASE WHEN soi.item_type = 'labor' THEN soi.labor_time_minutes / 60.0 * soi.unit_price ELSE soi.quantity * soi.unit_price END) FROM service_order_items soi WHERE soi.service_order_id = so.service_order_id), 0)::numeric(12,2) AS total,
|
|
COALESCE((SELECT json_agg(json_build_object('item_type', soi.item_type, 'description', soi.description, 'part_number', soi.part_number, 'labor_time_minutes', soi.labor_time_minutes, 'quantity', soi.quantity, 'unit_price', soi.unit_price, 'line_total', CASE WHEN soi.item_type = 'labor' THEN soi.labor_time_minutes / 60.0 * soi.unit_price ELSE soi.quantity * soi.unit_price END) ORDER BY soi.created_at) FROM service_order_items soi WHERE soi.service_order_id = so.service_order_id), '[]') AS items,
|
|
COALESCE(e.first_name || ' ' || e.last_name, 'Unassigned') AS technician
|
|
FROM service_orders so
|
|
JOIN vehicles v ON v.vehicle_id = so.vehicle_id
|
|
JOIN customers c ON c.customer_id = v.customer_id
|
|
LEFT JOIN employees e ON e.employee_id = so.assigned_employee_id
|
|
ORDER BY so.opened_at DESC
|
|
LIMIT 100
|
|
`);
|
|
response.json(result.rows);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to load service records.' }); }
|
|
});
|
|
|
|
app.get('/api/vehicles', async (_request, response) => {
|
|
try {
|
|
const result = await pool.query(`
|
|
SELECT v.vehicle_id, v.registration_number AS license_plate, btrim(v.vin) AS vin,
|
|
v.make, v.model, v.model_year, v.engine, v.transmission, v.fuel_type, v.color, v.mileage,
|
|
c.first_name || ' ' || c.last_name AS customer_name, c.email AS customer_email,
|
|
COUNT(so.service_order_id)::int AS service_record_count
|
|
FROM vehicles v
|
|
JOIN customers c ON c.customer_id = v.customer_id
|
|
LEFT JOIN service_orders so ON so.vehicle_id = v.vehicle_id
|
|
GROUP BY v.vehicle_id, c.customer_id
|
|
ORDER BY v.created_at DESC
|
|
`);
|
|
response.json(result.rows);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to load vehicles.' }); }
|
|
});
|
|
|
|
app.get('/api/vehicles/lookup', async (request, response) => {
|
|
const identifier = String(request.query.identifier || '').trim();
|
|
if (!identifier) return response.status(400).json({ error: 'Enter a VIN or license plate.' });
|
|
try {
|
|
const result = await pool.query(`
|
|
SELECT v.vehicle_id, v.registration_number AS license_plate, btrim(v.vin) AS vin,
|
|
v.make, v.model, v.model_year, v.engine, v.transmission, v.fuel_type, v.color, v.mileage,
|
|
c.first_name || ' ' || c.last_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone
|
|
FROM vehicles v JOIN customers c ON c.customer_id = v.customer_id
|
|
WHERE UPPER(v.registration_number) = UPPER($1) OR btrim(v.vin) = UPPER($1)
|
|
LIMIT 1
|
|
`, [identifier]);
|
|
if (!result.rowCount) return response.status(404).json({ error: 'No vehicle found for that VIN or license plate.' });
|
|
response.json(result.rows[0]);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to look up vehicle.' }); }
|
|
});
|
|
|
|
app.post('/api/vehicles', async (request, response) => {
|
|
const { customerName, customerEmail, customerPhone, vin, licensePlate, make, model, modelYear, engine, transmission, fuelType, color, mileage } = request.body;
|
|
const parsedMileage = Number(mileage || 0);
|
|
if (!customerName || !vin || !licensePlate || !make || !model || !Number.isInteger(parsedMileage) || parsedMileage < 0) return response.status(400).json({ error: 'Customer, VIN, license plate, make, model, and non-negative mileage are required.' });
|
|
const customerParts = customerName.trim().split(/\s+/);
|
|
const firstName = customerParts.shift();
|
|
const lastName = customerParts.join(' ') || firstName;
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
const existingCustomer = customerEmail?.trim() ? await client.query(`SELECT customer_id FROM customers WHERE LOWER(email) = LOWER($1) LIMIT 1`, [customerEmail.trim()]) : { rowCount: 0, rows: [] };
|
|
let customerId;
|
|
if (existingCustomer.rowCount) {
|
|
customerId = existingCustomer.rows[0].customer_id;
|
|
await client.query(`UPDATE customers SET first_name = $1, last_name = $2, phone = NULLIF($3, '') WHERE customer_id = $4`, [firstName, lastName, customerPhone?.trim() || '', customerId]);
|
|
} else {
|
|
const customer = await client.query(`INSERT INTO customers (first_name, last_name, email, phone) VALUES ($1, $2, NULLIF($3, ''), NULLIF($4, '')) RETURNING customer_id`, [firstName, lastName, customerEmail?.trim() || '', customerPhone?.trim() || '']);
|
|
customerId = customer.rows[0].customer_id;
|
|
}
|
|
const vehicle = await client.query(`INSERT INTO vehicles (customer_id, registration_number, make, model, model_year, engine, transmission, fuel_type, color, vin, mileage) VALUES ($1, $2, $3, $4, NULLIF($5, '')::integer, NULLIF($6, ''), NULLIF($7, ''), NULLIF($8, ''), NULLIF($9, ''), $10, $11) RETURNING vehicle_id`, [customerId, licensePlate.trim(), make.trim(), model.trim(), modelYear || '', engine?.trim() || '', transmission?.trim() || '', fuelType?.trim() || '', color?.trim() || '', vin.trim(), parsedMileage]);
|
|
await client.query('COMMIT');
|
|
response.status(201).json({ vehicle_id: vehicle.rows[0].vehicle_id });
|
|
} catch (error) {
|
|
await client.query('ROLLBACK');
|
|
const status = error.code === '23505' ? 409 : 500;
|
|
const message = error.constraint === 'vehicles_vin_key' ? 'That VIN is already registered.' : error.constraint === 'vehicles_registration_number_key' ? 'That license plate is already registered.' : status === 409 ? 'A customer with that email already exists.' : 'Unable to create vehicle.';
|
|
response.status(status).json({ error: message });
|
|
} finally { client.release(); }
|
|
});
|
|
|
|
app.post('/api/service-records', async (request, response) => {
|
|
const { customerName, customerEmail, customerPhone, vin, licensePlate, mileage, serviceDate, technicianName, complaint, make, model, modelYear, engine, transmission, fuelType, color } = request.body;
|
|
let items;
|
|
try { items = normalizeItems(request.body.items); } catch (error) { return response.status(400).json({ error: error.message }); }
|
|
const parsedMileage = Number(mileage);
|
|
if (!customerName || !vin || !licensePlate || !technicianName || !complaint || !serviceDate || !Number.isInteger(parsedMileage) || parsedMileage < 0) {
|
|
return response.status(400).json({ error: 'Customer, VIN, license plate, non-negative mileage, service date, technician, and complaint are required.' });
|
|
}
|
|
|
|
const customerParts = customerName.trim().split(/\s+/);
|
|
const firstName = customerParts.shift();
|
|
const lastName = customerParts.join(' ') || firstName;
|
|
const technicianParts = technicianName.trim().split(/\s+/);
|
|
const technicianFirstName = technicianParts.shift();
|
|
const technicianLastName = technicianParts.join(' ') || technicianFirstName;
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
const existingVehicle = await client.query(`SELECT vehicle_id, customer_id FROM vehicles WHERE UPPER(registration_number) = UPPER($1) OR btrim(vin) = UPPER($2) LIMIT 1`, [licensePlate.trim(), vin.trim()]);
|
|
let customerId;
|
|
if (existingVehicle.rowCount) {
|
|
customerId = existingVehicle.rows[0].customer_id;
|
|
await client.query(`UPDATE customers SET first_name = $1, last_name = $2, email = NULLIF($3, ''), phone = NULLIF($4, '') WHERE customer_id = $5`, [firstName, lastName, customerEmail?.trim() || '', customerPhone?.trim() || '', customerId]);
|
|
} else {
|
|
const existingCustomer = customerEmail?.trim()
|
|
? await client.query(`SELECT customer_id FROM customers WHERE LOWER(email) = LOWER($1) LIMIT 1`, [customerEmail.trim()])
|
|
: { rowCount: 0, rows: [] };
|
|
if (existingCustomer.rowCount) {
|
|
customerId = existingCustomer.rows[0].customer_id;
|
|
await client.query(`UPDATE customers SET first_name = $1, last_name = $2, phone = NULLIF($3, '') WHERE customer_id = $4`, [firstName, lastName, customerPhone?.trim() || '', customerId]);
|
|
} else {
|
|
const customer = await client.query(`INSERT INTO customers (first_name, last_name, email, phone) VALUES ($1, $2, NULLIF($3, ''), NULLIF($4, '')) RETURNING customer_id`, [firstName, lastName, customerEmail?.trim() || '', customerPhone?.trim() || '']);
|
|
customerId = customer.rows[0].customer_id;
|
|
}
|
|
}
|
|
const technician = await client.query(
|
|
`INSERT INTO employees (first_name, last_name, role) VALUES ($1, $2, 'technician') RETURNING employee_id`,
|
|
[technicianFirstName, technicianLastName]
|
|
);
|
|
const vehicle = existingVehicle.rowCount
|
|
? await client.query(`UPDATE vehicles SET customer_id = $1, registration_number = $2, make = COALESCE(NULLIF($3, ''), make), model = COALESCE(NULLIF($4, ''), model), model_year = NULLIF($5, '')::integer, engine = NULLIF($6, ''), transmission = NULLIF($7, ''), fuel_type = NULLIF($8, ''), color = NULLIF($9, ''), vin = $10, mileage = $11 WHERE vehicle_id = $12 RETURNING vehicle_id`, [customerId, licensePlate.trim(), make?.trim() || '', model?.trim() || '', modelYear || '', engine?.trim() || '', transmission?.trim() || '', fuelType?.trim() || '', color?.trim() || '', vin.trim(), parsedMileage, existingVehicle.rows[0].vehicle_id])
|
|
: await client.query(`INSERT INTO vehicles (customer_id, registration_number, make, model, model_year, engine, transmission, fuel_type, color, vin, mileage) VALUES ($1, $2, NULLIF($3, ''), NULLIF($4, ''), NULLIF($5, '')::integer, NULLIF($6, ''), NULLIF($7, ''), NULLIF($8, ''), NULLIF($9, ''), $10, $11) RETURNING vehicle_id`, [customerId, licensePlate.trim(), make?.trim() || 'Not specified', model?.trim() || 'Not specified', modelYear || '', engine?.trim() || '', transmission?.trim() || '', fuelType?.trim() || '', color?.trim() || '', vin.trim(), parsedMileage]);
|
|
const record = await client.query(
|
|
`INSERT INTO service_orders (vehicle_id, assigned_employee_id, complaint, service_date) VALUES ($1, $2, $3, $4) RETURNING service_order_id`,
|
|
[vehicle.rows[0].vehicle_id, technician.rows[0].employee_id, complaint.trim(), serviceDate]
|
|
);
|
|
await insertItems(client, record.rows[0].service_order_id, items);
|
|
await client.query('COMMIT');
|
|
response.status(201).json({ service_order_id: record.rows[0].service_order_id });
|
|
} catch (error) {
|
|
await client.query('ROLLBACK');
|
|
const status = error.code === '23505' ? 409 : 500;
|
|
const message = error.constraint === 'vehicles_vin_key' ? 'That VIN is already registered.' : error.constraint === 'vehicles_registration_number_key' ? 'That license plate is already registered.' : status === 409 ? 'A record with one of these values already exists.' : 'Unable to create service record.';
|
|
response.status(status).json({ error: message });
|
|
} finally { client.release(); }
|
|
});
|
|
|
|
app.patch('/api/service-records/:id', async (request, response) => {
|
|
const { customerName, customerEmail, customerPhone, vin, licensePlate, mileage, serviceDate, technicianName, complaint, make, model, modelYear, engine, transmission, fuelType, color } = request.body;
|
|
let items;
|
|
try { items = normalizeItems(request.body.items); } catch (error) { return response.status(400).json({ error: error.message }); }
|
|
const parsedMileage = Number(mileage);
|
|
if (!customerName || !vin || !licensePlate || !technicianName || !complaint || !serviceDate || !Number.isInteger(parsedMileage) || parsedMileage < 0) return response.status(400).json({ error: 'Customer, VIN, license plate, non-negative mileage, service date, technician, and complaint are required.' });
|
|
const customerParts = customerName.trim().split(/\s+/);
|
|
const technicianParts = technicianName.trim().split(/\s+/);
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
const current = await client.query(`SELECT c.customer_id, v.vehicle_id, so.assigned_employee_id FROM service_orders so JOIN vehicles v ON v.vehicle_id = so.vehicle_id JOIN customers c ON c.customer_id = v.customer_id WHERE so.service_order_id = $1`, [request.params.id]);
|
|
if (!current.rowCount) { await client.query('ROLLBACK'); return response.status(404).json({ error: 'Service record not found.' }); }
|
|
const customerFirst = customerParts.shift();
|
|
const technicianFirst = technicianParts.shift();
|
|
await client.query('UPDATE customers SET first_name = $1, last_name = $2, email = NULLIF($3, \'\'), phone = NULLIF($4, \'\') WHERE customer_id = $5', [customerFirst, customerParts.join(' ') || customerFirst, customerEmail?.trim() || '', customerPhone?.trim() || '', current.rows[0].customer_id]);
|
|
await client.query('UPDATE employees SET first_name = $1, last_name = $2 WHERE employee_id = $3', [technicianFirst, technicianParts.join(' ') || technicianFirst, current.rows[0].assigned_employee_id]);
|
|
await client.query(`UPDATE vehicles SET vin = $1, registration_number = $2, make = NULLIF($3, ''), model = NULLIF($4, ''), model_year = NULLIF($5, '')::integer, engine = NULLIF($6, ''), transmission = NULLIF($7, ''), fuel_type = NULLIF($8, ''), color = NULLIF($9, ''), mileage = $10 WHERE vehicle_id = $11`, [vin.trim(), licensePlate.trim(), make?.trim() || '', model?.trim() || '', modelYear || '', engine?.trim() || '', transmission?.trim() || '', fuelType?.trim() || '', color?.trim() || '', parsedMileage, current.rows[0].vehicle_id]);
|
|
await client.query('UPDATE service_orders SET complaint = $1, service_date = $2 WHERE service_order_id = $3', [complaint.trim(), serviceDate, request.params.id]);
|
|
await client.query('DELETE FROM service_order_items WHERE service_order_id = $1', [request.params.id]);
|
|
await insertItems(client, request.params.id, items);
|
|
await client.query('COMMIT');
|
|
response.json({ service_order_id: request.params.id });
|
|
} catch (error) {
|
|
await client.query('ROLLBACK');
|
|
response.status(error.code === '23505' ? 409 : 500).json({ error: error.code === '23505' ? 'That VIN or license plate is already registered.' : 'Unable to update service record.' });
|
|
} finally { client.release(); }
|
|
});
|
|
|
|
app.get('/api/customers', async (_request, response) => {
|
|
try {
|
|
const result = await pool.query(`SELECT c.customer_id, c.first_name, c.last_name, c.email, c.phone, c.created_at, COUNT(v.vehicle_id)::int AS vehicle_count FROM customers c LEFT JOIN vehicles v ON v.customer_id = c.customer_id GROUP BY c.customer_id ORDER BY c.created_at DESC LIMIT 50`);
|
|
response.json(result.rows);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to load customers.' }); }
|
|
});
|
|
|
|
app.get('/api/customers/lookup', async (request, response) => {
|
|
const email = String(request.query.email || '').trim();
|
|
const name = String(request.query.name || '').trim();
|
|
if (!email && !name) return response.status(400).json({ error: 'Enter a customer email or name.' });
|
|
try {
|
|
const result = email
|
|
? await pool.query(`SELECT customer_id, first_name, last_name, email, phone FROM customers WHERE LOWER(email) = LOWER($1) LIMIT 1`, [email])
|
|
: await pool.query(`SELECT customer_id, first_name, last_name, email, phone FROM customers WHERE LOWER(first_name || ' ' || last_name) = LOWER($1) LIMIT 2`, [name]);
|
|
if (!result.rowCount) return response.status(404).json({ error: 'No customer found.' });
|
|
if (!email && result.rowCount > 1) return response.status(409).json({ error: 'More than one customer has that name. Use email to identify the customer.' });
|
|
response.json(result.rows[0]);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to look up customer.' }); }
|
|
});
|
|
|
|
app.post('/api/customers', async (request, response) => {
|
|
const { firstName, lastName, email, phone } = request.body;
|
|
if (!firstName || !lastName) return response.status(400).json({ error: 'First name and last name are required.' });
|
|
try {
|
|
const result = await pool.query(`INSERT INTO customers (first_name, last_name, email, phone) VALUES ($1, $2, NULLIF($3, ''), NULLIF($4, '')) RETURNING customer_id, first_name, last_name, email, phone`, [firstName.trim(), lastName.trim(), email?.trim() || '', phone?.trim() || '']);
|
|
response.status(201).json(result.rows[0]);
|
|
} catch (error) { response.status(error.code === '23505' ? 409 : 500).json({ error: error.code === '23505' ? 'That email is already registered.' : 'Unable to create customer.' }); }
|
|
});
|
|
|
|
app.patch('/api/orders/:id/status', async (request, response) => {
|
|
const allowedStatuses = ['open', 'in_progress', 'waiting_for_parts', 'ready', 'closed', 'cancelled'];
|
|
if (!allowedStatuses.includes(request.body.status)) return response.status(400).json({ error: 'Invalid service order status.' });
|
|
try {
|
|
const result = await pool.query(`UPDATE service_orders SET status = $1, closed_at = CASE WHEN $1 = 'closed' THEN NOW() ELSE NULL END WHERE service_order_id = $2 RETURNING service_order_id, status`, [request.body.status, request.params.id]);
|
|
if (!result.rowCount) return response.status(404).json({ error: 'Service order not found.' });
|
|
response.json(result.rows[0]);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to update service order.' }); }
|
|
});
|
|
|
|
app.get('/api/users', async (_request, response) => {
|
|
try {
|
|
const result = await pool.query('SELECT user_id, username, display_name, email, role, can_read, can_add, can_delete, created_at FROM app_users ORDER BY created_at DESC');
|
|
response.json(result.rows);
|
|
} catch (error) { response.status(500).json({ error: 'Unable to load user accounts.' }); }
|
|
});
|
|
|
|
app.post('/api/users', async (request, response) => {
|
|
const { username, displayName, email, role, canRead, canAdd, canDelete, password } = request.body;
|
|
const allowedRoles = ['admin', 'service_advisor', 'technician', 'viewer'];
|
|
if (!username || !displayName || !allowedRoles.includes(role) || !password) return response.status(400).json({ error: 'Username, display name, valid role, and password are required.' });
|
|
try {
|
|
const passwordHash = await hashPassword(password);
|
|
const result = await pool.query(`INSERT INTO app_users (username, display_name, email, role, can_read, can_add, can_delete, password_hash) VALUES ($1, $2, NULLIF($3, ''), $4, $5, $6, $7, $8) RETURNING user_id, username, display_name, email, role, can_read, can_add, can_delete, created_at`, [username.trim(), displayName.trim(), email?.trim() || '', role, canRead !== false, canAdd === true || ['technician', 'service_advisor'].includes(role), role === 'admin' && canDelete === true, passwordHash]);
|
|
response.status(201).json(result.rows[0]);
|
|
} catch (error) { response.status(error.code === '23505' ? 409 : 500).json({ error: error.code === '23505' ? 'That username or email is already in use.' : 'Unable to create user account.' }); }
|
|
});
|
|
|
|
app.patch('/api/users/:id', async (request, response) => {
|
|
const { displayName, email, role, canRead, canAdd, canDelete, password } = request.body;
|
|
const allowedRoles = ['admin', 'service_advisor', 'technician', 'viewer'];
|
|
if (!displayName || !allowedRoles.includes(role)) return response.status(400).json({ error: 'Display name and a valid role are required.' });
|
|
try {
|
|
const passwordHash = password ? await hashPassword(password) : null;
|
|
const result = await pool.query(`UPDATE app_users SET display_name = $1, email = NULLIF($2, ''), role = $3, can_read = $4, can_add = $5, can_delete = $6, password_hash = COALESCE($7, password_hash) WHERE user_id = $8 RETURNING user_id`, [displayName.trim(), email?.trim() || '', role, canRead === true, canAdd === true, canDelete === true, passwordHash, request.params.id]);
|
|
if (!result.rowCount) return response.status(404).json({ error: 'User account not found.' });
|
|
response.json(result.rows[0]);
|
|
} catch (error) { response.status(error.code === '23505' ? 409 : 500).json({ error: error.code === '23505' ? 'That email is already in use.' : 'Unable to update user account.' }); }
|
|
});
|
|
|
|
app.delete('/api/users/:id', async (request, response) => {
|
|
try {
|
|
const user = await pool.query('SELECT role FROM app_users WHERE user_id = $1', [request.params.id]);
|
|
if (!user.rowCount) return response.status(404).json({ error: 'User account not found.' });
|
|
if (user.rows[0].role === 'admin') {
|
|
const admins = await pool.query("SELECT COUNT(*)::int AS count FROM app_users WHERE role = 'admin'");
|
|
if (admins.rows[0].count <= 1) return response.status(409).json({ error: 'The last administrator cannot be deleted.' });
|
|
}
|
|
await pool.query('DELETE FROM app_users WHERE user_id = $1', [request.params.id]);
|
|
response.status(204).end();
|
|
} catch (error) { response.status(500).json({ error: 'Unable to delete user account.' }); }
|
|
});
|
|
|
|
app.use((_request, response) => response.sendFile(path.join(__dirname, 'public', 'index.html')));
|
|
|
|
async function start() {
|
|
await pool.query(`ALTER TABLE vehicles ADD COLUMN IF NOT EXISTS engine TEXT, ADD COLUMN IF NOT EXISTS transmission TEXT, ADD COLUMN IF NOT EXISTS fuel_type TEXT, ADD COLUMN IF NOT EXISTS color TEXT`);
|
|
await pool.query(`ALTER TABLE service_order_items ADD COLUMN IF NOT EXISTS part_number TEXT, ADD COLUMN IF NOT EXISTS labor_time_minutes INTEGER`);
|
|
await pool.query(`ALTER TABLE service_orders ADD COLUMN IF NOT EXISTS service_date DATE`);
|
|
await pool.query(`UPDATE service_orders SET service_date = opened_at::date WHERE service_date IS NULL`);
|
|
await pool.query(`ALTER TABLE service_orders ALTER COLUMN service_date SET DEFAULT CURRENT_DATE`);
|
|
await pool.query(`ALTER TABLE service_orders ALTER COLUMN service_date SET NOT NULL`);
|
|
await pool.query(`CREATE TABLE IF NOT EXISTS app_users (user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), username TEXT NOT NULL UNIQUE, display_name TEXT NOT NULL, email TEXT UNIQUE, role TEXT NOT NULL DEFAULT 'viewer' CHECK (role IN ('admin', 'service_advisor', 'technician', 'viewer')), can_read BOOLEAN NOT NULL DEFAULT TRUE, can_add BOOLEAN NOT NULL DEFAULT FALSE, can_delete BOOLEAN NOT NULL DEFAULT FALSE, password_hash TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW())`);
|
|
await pool.query('ALTER TABLE app_users ADD COLUMN IF NOT EXISTS password_hash TEXT');
|
|
const adminPasswordHash = await hashPassword(process.env.ADMIN_PASSWORD || 'change-me-in-production');
|
|
await pool.query("INSERT INTO app_users (username, display_name, role, can_read, can_add, can_delete, password_hash) VALUES ('admin', 'Workshop administrator', 'admin', TRUE, TRUE, TRUE, $1) ON CONFLICT (username) DO UPDATE SET password_hash = COALESCE(app_users.password_hash, EXCLUDED.password_hash)", [adminPasswordHash]);
|
|
app.listen(port, () => console.log(`Car service web app listening on port ${port}`));
|
|
}
|
|
|
|
start().catch((error) => { console.error('Unable to initialize user accounts.', error); process.exit(1); }); |