source: server.js@ 6149556

main
Last change on this file since 6149556 was 06ebe74, checked in by Klimentina Efremova <klimentina08642@…>, 9 days ago

Created successfull connection to university database through SSH tunnel
  • Property mode set to 100644
File size: 255.2 KB
Line 
1const http = require('http');
2const url = require('url');
3const { Pool } = require('pg');
4const fs = require('fs');
5const path = require('path');
6const crypto = require('crypto');
7const nodemailer = require('nodemailer');
8const bcrypt = require('bcryptjs');
9require('dotenv').config();
10
11const port = process.env.PORT || 3000;
12
13const sessions = new Map();
14const verificationCodes = new Map();
15const tempUsers = new Map();
16const tempAdminSessions = new Map();
17const tempStoreRegistrations = new Map();
18
19console.log('🔧 Starting Handcraft Marketplace Server...');
20console.log('🎨 Colors: Royal Blue & Pink Theme');
21
22let emailTransporter;
23
24if (process.env.SMTP_USER && process.env.SMTP_PASS) {
25 const emailConfig = {
26 host: process.env.SMTP_HOST || 'smtp.gmail.com',
27 port: (() => {
28 const configuredPort = parseInt(process.env.SMTP_PORT, 10);
29 if (process.env.SMTP_HOST === 'smtp.ethereal.email' && configuredPort === 3000) return 587;
30 return configuredPort || 587;
31 })(),
32 secure: false,
33 auth: {
34 user: process.env.SMTP_USER,
35 pass: process.env.SMTP_PASS
36 }
37 };
38
39 emailTransporter = nodemailer.createTransport(emailConfig);
40
41 emailTransporter.verify(function(error, success) {
42 if (error) {
43 console.log('❌ Email configuration failed:', error.message);
44 console.log('📧 Falling back to console display for verification codes');
45 emailTransporter = createMockTransporter();
46 } else {
47 console.log('✅ Email server is ready to send real emails!');
48 }
49 });
50} else {
51 console.log('📧 No email credentials found. Verification codes will be shown in console.');
52 emailTransporter = createMockTransporter();
53}
54
55function createMockTransporter() {
56 return {
57 sendMail: function(mailOptions) {
58 return new Promise((resolve, reject) => {
59 const codeMatch = mailOptions.html.match(/\b\d{6}\b/);
60 const code = codeMatch ? codeMatch[0] : 'unknown';
61
62 console.log('');
63 console.log('🎯 ===== VERIFICATION CODE =====');
64 console.log('📧 For:', mailOptions.to);
65 console.log('🔐 CODE:', code);
66 console.log('⏰ Expires in: 30 seconds');
67 console.log('📝 Use this code to continue');
68 console.log('================================');
69 console.log('');
70
71 resolve({ messageId: 'dev-' + Date.now() });
72 });
73 }
74 };
75}
76
77function sendVerificationEmail(toEmail, code) {
78 const mailOptions = {
79 from: process.env.SMTP_USER || 'noreply@handcraft-marketplace.com',
80 to: toEmail,
81 subject: 'Your Verification Code - Handcraft Marketplace',
82 html: `
83 <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; background: linear-gradient(135deg, #4169E1 0%, #FF69B4 100%); color: white; padding: 20px; border-radius: 10px;">
84 <h2 style="text-align: center;">🎨 Handcraft Marketplace</h2>
85 <div style="background-color: white; color: #333; padding: 20px; border-radius: 8px; margin: 20px 0;">
86 <h3 style="color: #4169E1;">Account Verification</h3>
87 <p>Your verification code is:</p>
88 <div style="background-color: #f5f5f5; padding: 15px; border-radius: 5px; text-align: center; font-size: 24px; font-weight: bold; letter-spacing: 5px; margin: 20px 0; color: #4169E1;">
89 ${code}
90 </div>
91 <p style="color: #e74c3c; font-weight: bold;">⚠️ This code will expire in 30 seconds</p>
92 <p style="color: #666; font-size: 12px;">If you didn't request this verification, please ignore this email.</p>
93 </div>
94 </div>`
95 };
96
97 console.log('');
98 console.log('🎯 ===== VERIFICATION CODE FOR TESTING =====');
99 console.log('📧 Email:', toEmail);
100 console.log('🔐 CODE:', code);
101 console.log('⏰ Expires in: 30 seconds');
102 console.log('==========================================');
103 console.log('');
104
105 return emailTransporter.sendMail(mailOptions);
106}
107
108function send2FACode(toEmail, code) {
109 const mailOptions = {
110 from: process.env.SMTP_USER || 'noreply@handcraft-marketplace.com',
111 to: toEmail,
112 subject: 'Your 2FA Code - Handcraft Marketplace',
113 html: `
114 <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; background: linear-gradient(135deg, #4169E1 0%, #FF69B4 100%); color: white; padding: 20px; border-radius: 10px;">
115 <h2 style="text-align: center;">🎨 Handcraft Marketplace</h2>
116 <div style="background-color: white; color: #333; padding: 20px; border-radius: 8px; margin: 20px 0;">
117 <h3 style="color: #4169E1;">Two-Factor Authentication</h3>
118 <p>Your login verification code is:</p>
119 <div style="background-color: #f5f5f5; padding: 15px; border-radius: 5px; text-align: center; font-size: 24px; font-weight: bold; letter-spacing: 5px; margin: 20px 0; color: #4169E1;">
120 ${code}
121 </div>
122 <p style="color: #e74c3c; font-weight: bold;">⚠️ This code will expire in 30 seconds</p>
123 <p style="color: #666; font-size: 12px;">If you're not trying to login, please secure your account immediately.</p>
124 </div>
125 </div>`
126 };
127
128 console.log('');
129 console.log('🎯 ===== 2FA CODE FOR TESTING =====');
130 console.log('📧 Email:', toEmail);
131 console.log('🔐 CODE:', code);
132 console.log('⏰ Expires in: 30 seconds');
133 console.log('==================================');
134 console.log('');
135
136 return emailTransporter.sendMail(mailOptions);
137}
138
139function sendStoreRegistrationEmail(toEmail, code, storeName) {
140 const mailOptions = {
141 from: process.env.SMTP_USER || 'noreply@handcraft-marketplace.com',
142 to: toEmail,
143 subject: 'Store Registration Verification - Handcraft Marketplace',
144 html: `
145 <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; background: linear-gradient(135deg, #4169E1 0%, #FF69B4 100%); color: white; padding: 20px; border-radius: 10px;">
146 <h2 style="text-align: center;">🎨 Handcraft Marketplace</h2>
147 <div style="background-color: white; color: #333; padding: 20px; border-radius: 8px; margin: 20px 0;">
148 <h3 style="color: #4169E1;">Store Registration Verification</h3>
149 <p>Thank you for registering your store "<strong>${storeName}</strong>" on Handcraft Marketplace!</p>
150 <p>Your verification code is:</p>
151 <div style="background-color: #f5f5f5; padding: 15px; border-radius: 5px; text-align: center; font-size: 24px; font-weight: bold; letter-spacing: 5px; margin: 20px 0; color: #4169E1;">
152 ${code}
153 </div>
154 <p style="color: #e74c3c; font-weight: bold;">⚠️ This code will expire in 30 seconds</p>
155 <p style="color: #666; font-size: 12px;">If you didn't request this verification, please ignore this email.</p>
156 </div>
157 </div>`
158 };
159
160 console.log('');
161 console.log('🎯 ===== STORE REGISTRATION VERIFICATION CODE =====');
162 console.log('📧 For:', toEmail);
163 console.log('🏪 Store:', storeName);
164 console.log('🔐 CODE:', code);
165 console.log('⏰ Expires in: 30 seconds');
166 console.log('==================================================');
167 console.log('');
168
169 return emailTransporter.sendMail(mailOptions);
170}
171
172function generateVerificationCode() {
173 let code = '';
174 for(let i = 0; i < 6; i++) {
175 code += crypto.randomInt(0, 10);
176 }
177 return code;
178}
179
180function generateSessionId() {
181 return crypto.randomBytes(32).toString('hex');
182}
183
184function serveStaticFile(res, filePath, contentType) {
185 const fullPath = path.join(__dirname, 'interfejs', filePath);
186 fs.readFile(fullPath, (err, data) => {
187 if (err) {
188 console.error('File not found:', fullPath, err);
189 res.writeHead(404, { 'Content-Type': 'text/plain' });
190 res.end('File not found');
191 } else {
192 res.writeHead(200, { 'Content-Type': contentType });
193 res.end(data);
194 }
195 });
196}
197
198function parseCookies(req) {
199 const cookieHeader = req.headers.cookie;
200 const cookies = {};
201 if (cookieHeader) {
202 cookieHeader.split(';').forEach(cookie => {
203 const parts = cookie.split('=');
204 cookies[parts[0].trim()] = parts[1]?.trim();
205 });
206 }
207 return cookies;
208}
209
210function getClientIp(req) {
211 return req.headers['x-forwarded-for'] ||
212 req.connection.remoteAddress ||
213 req.socket.remoteAddress ||
214 (req.connection.socket ? req.connection.socket.remoteAddress : null);
215}
216
217function requireAuth(req, res, callback) {
218 const cookies = parseCookies(req);
219 const sessionId = cookies.sessionId;
220
221 if (!sessionId || !sessions.has(sessionId)) {
222 res.writeHead(302, { 'Location': '/login.html' });
223 res.end();
224 return;
225 }
226
227 const userId = sessions.get(sessionId);
228
229 if (tempAdminSessions.has(sessionId)) {
230 if (!req.url.includes('/change-password') && !req.url.includes('/api/force-change-password')) {
231 res.writeHead(302, { 'Location': '/change-password.html?forced=true' });
232 res.end();
233 return;
234 }
235 }
236
237 callback(userId);
238}
239
240function requireRole(roleName) {
241 return function(req, res, callback) {
242 requireAuth(req, res, (userId) => {
243 database.getUserById(userId, (err, user) => {
244 if (err || !user) {
245 res.writeHead(403, { 'Content-Type': 'application/json' });
246 res.end(JSON.stringify({ success: false, message: 'Access denied' }));
247 return;
248 }
249
250 const hasRole = user.roles && user.roles.some(role => role.name === roleName);
251
252 if (!hasRole) {
253 res.writeHead(403, { 'Content-Type': 'application/json' });
254 res.end(JSON.stringify({ success: false, message: 'Insufficient permissions' }));
255 return;
256 }
257
258 callback(userId, user);
259 });
260 });
261 };
262}
263
264function validateEmail(email) {
265 const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
266 return emailRegex.test(email);
267}
268
269function validatePassword(password) {
270 const passwordRegex = /^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])[A-Za-z\d@$!%*?&]{8,}$/;
271 return passwordRegex.test(password);
272}
273
274function cleanupExpiredCodes() {
275 const now = Date.now();
276 let cleanedCount = 0;
277
278 for (const [key, data] of verificationCodes.entries()) {
279 if (now - data.timestamp > 30 * 1000) {
280 verificationCodes.delete(key);
281 cleanedCount++;
282 }
283 }
284
285 for (const [key, data] of tempUsers.entries()) {
286 if (now - data.timestamp > 30 * 1000) {
287 tempUsers.delete(key);
288 cleanedCount++;
289 }
290 }
291
292 for (const [key, data] of tempStoreRegistrations.entries()) {
293 if (now - data.timestamp > 30 * 1000) {
294 tempStoreRegistrations.delete(key);
295 cleanedCount++;
296 }
297 }
298
299 if (cleanedCount > 0) {
300 console.log(`🧹 Cleaned ${cleanedCount} expired verification codes`);
301 }
302}
303
304setInterval(cleanupExpiredCodes, 10 * 1000);
305
306function requireStoreOwner() {
307 return function(req, res, callback) {
308 requireAuth(req, res, (userId) => {
309 const userIdStr = String(userId);
310
311 // Check if this is the admin user (ID 000000)
312 if (userIdStr === '000000') {
313 // Admin is not a store owner
314 res.writeHead(403, { 'Content-Type': 'application/json' });
315 res.end(JSON.stringify({ success: false, message: 'Access denied - not a store owner' }));
316 return;
317 }
318
319 // Check if it's a personal user
320 if (userIdStr.startsWith('personal_')) {
321 const personalId = userIdStr.replace('personal_', '');
322
323 database.database.get(
324 'SELECT boss_id FROM boss WHERE boss_id = $1',
325 [personalId],
326 (err, boss) => {
327 if (err || !boss) {
328 res.writeHead(403, { 'Content-Type': 'application/json' });
329 res.end(JSON.stringify({ success: false, message: 'Access denied - not a store owner' }));
330 return;
331 }
332
333 callback(personalId);
334 }
335 );
336 } else {
337 // Not a personal user, so not a store owner
338 res.writeHead(403, { 'Content-Type': 'application/json' });
339 res.end(JSON.stringify({ success: false, message: 'Access denied - not a store owner' }));
340 }
341 });
342 };
343}
344
345
346
347const pool = new Pool({
348 connectionString: process.env.DATABASE_URL,
349 host: process.env.PGHOST || process.env.DB_HOST || 'localhost',
350 port: Number(process.env.PGPORT || process.env.DB_PORT || 5432),
351 user: process.env.PGUSER || process.env.DB_USER || 'postgres',
352 password: process.env.PGPASSWORD || process.env.DB_PASSWORD || '',
353 database: process.env.PGDATABASE || process.env.DB_NAME || 'handcraft_marketplace',
354 max: Number(process.env.PG_POOL_MAX || 10),
355 idleTimeoutMillis: 30000
356});
357
358let transactionClient = null;
359
360function dbQuery(sql, params = [], callback) {
361 const client = transactionClient || pool;
362 client.query(sql, params)
363 .then(result => callback(null, result))
364 .catch(err => callback(err));
365}
366
367const database = {
368 database: {
369 get(sql, params, callback) {
370 if (typeof params === 'function') {
371 callback = params;
372 params = [];
373 }
374 dbQuery(sql, params || [], (err, result) => {
375 callback(err, result && result.rows ? result.rows[0] : undefined);
376 });
377 },
378 all(sql, params, callback) {
379 if (typeof params === 'function') {
380 callback = params;
381 params = [];
382 }
383 dbQuery(sql, params || [], (err, result) => {
384 callback(err, result ? result.rows : []);
385 });
386 },
387 run(sql, params, callback) {
388 if (typeof params === 'function') {
389 callback = params;
390 params = [];
391 }
392 const normalized = String(sql).trim().replace(/;\s*$/, '').toUpperCase();
393
394 if (normalized === 'BEGIN TRANSACTION' || normalized === 'BEGIN') {
395 if (transactionClient) {
396 callback?.(null);
397 return;
398 }
399 pool.connect().then(client => {
400 transactionClient = client;
401 return client.query('BEGIN');
402 }).then(() => callback?.(null))
403 .catch(err => {
404 if (transactionClient) transactionClient.release();
405 transactionClient = null;
406 callback?.(err);
407 });
408 return;
409 }
410
411 if (normalized === 'COMMIT') {
412 if (!transactionClient) {
413 callback?.(null);
414 return;
415 }
416 const client = transactionClient;
417 client.query('COMMIT')
418 .then(() => {
419 transactionClient = null;
420 client.release();
421 callback?.(null);
422 })
423 .catch(err => {
424 transactionClient = null;
425 client.release();
426 callback?.(err);
427 });
428 return;
429 }
430
431 if (normalized === 'ROLLBACK') {
432 if (!transactionClient) {
433 callback?.(null);
434 return;
435 }
436 const client = transactionClient;
437 client.query('ROLLBACK')
438 .then(() => {
439 transactionClient = null;
440 client.release();
441 callback?.(null);
442 })
443 .catch(err => {
444 transactionClient = null;
445 client.release();
446 callback?.(err);
447 });
448 return;
449 }
450
451 dbQuery(sql, params || [], (err, result) => {
452 if (callback) {
453 callback.call(
454 { changes: result ? result.rowCount : 0, lastID: result?.rows?.[0]?.id },
455 err
456 );
457 }
458 });
459 }
460 },
461
462 async initializeDatabase() {
463 // The database supplied by the project is authoritative. Existing tables
464 // are removed before recreation so an old incompatible schema can never
465 // survive a restart and cause CREATE TABLE IF NOT EXISTS to skip columns.
466 const schemaCompatibility = await pool.query(`
467 SELECT
468 EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='product') AS product_exists,
469 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='category_id') AS product_has_category,
470 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='store_id') AS product_has_store,
471 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='order' AND column_name='store_id') AS order_has_store,
472 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='request' AND column_name='store_id') AS request_has_store,
473 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='review' AND column_name='review_id') AS review_has_review_id,
474 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='refund' AND column_name='request_date') AS refund_has_request_date
475 `);
476
477 const c = schemaCompatibility.rows[0];
478 const forceReset = String(process.env.RESET_DATABASE || '').toLowerCase() === 'true' || process.env.RESET_DATABASE === '1';
479 const schemaMismatch = !c.product_exists || !c.product_has_category || c.product_has_store || c.order_has_store || c.request_has_store || c.review_has_review_id || c.refund_has_request_date;
480 const resetDatabase = forceReset || schemaMismatch;
481
482 if (resetDatabase) {
483 console.log('🧹 Existing PostgreSQL schema is incompatible with the project schema. Recreating project tables...');
484 await pool.query(`
485 DROP TABLE IF EXISTS audit_log CASCADE;
486 DROP TABLE IF EXISTS user_roles CASCADE;
487 DROP TABLE IF EXISTS roles CASCADE;
488 DROP TABLE IF EXISTS users CASCADE;
489 DROP TABLE IF EXISTS approves CASCADE;
490 DROP TABLE IF EXISTS includes CASCADE;
491 DROP TABLE IF EXISTS sells CASCADE;
492 DROP TABLE IF EXISTS worked CASCADE;
493 DROP TABLE IF EXISTS works_in_store CASCADE;
494 DROP TABLE IF EXISTS makes_change CASCADE;
495 DROP TABLE IF EXISTS "change" CASCADE;
496 DROP TABLE IF EXISTS for_store CASCADE;
497 DROP TABLE IF EXISTS answers CASCADE;
498 DROP TABLE IF EXISTS makes_request CASCADE;
499 DROP TABLE IF EXISTS request CASCADE;
500 DROP TABLE IF EXISTS exchanges_data CASCADE;
501 DROP TABLE IF EXISTS monthly_profit CASCADE;
502 DROP TABLE IF EXISTS report CASCADE;
503 DROP TABLE IF EXISTS refund CASCADE;
504 DROP TABLE IF EXISTS review CASCADE;
505 DROP TABLE IF EXISTS "order" CASCADE;
506 DROP TABLE IF EXISTS delivery_address CASCADE;
507 DROP TABLE IF EXISTS client CASCADE;
508 DROP TABLE IF EXISTS employees CASCADE;
509 DROP TABLE IF EXISTS boss CASCADE;
510 DROP TABLE IF EXISTS permissions CASCADE;
511 DROP TABLE IF EXISTS personal CASCADE;
512 DROP TABLE IF EXISTS color CASCADE;
513 DROP TABLE IF EXISTS image CASCADE;
514 DROP TABLE IF EXISTS product CASCADE;
515 DROP TABLE IF EXISTS store CASCADE;
516 DROP TABLE IF EXISTS category CASCADE;
517 `);
518 }
519
520 const schema = `
521 CREATE TABLE IF NOT EXISTS category (
522 id SERIAL PRIMARY KEY,
523 name VARCHAR(50) NOT NULL,
524 parent_category_id INTEGER REFERENCES category(id) NOT NULL
525 );
526
527 CREATE TABLE IF NOT EXISTS product (
528 code VARCHAR(8) PRIMARY KEY DEFAULT '-1',
529 price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
530 availability INTEGER NOT NULL,
531 weight DECIMAL(5,2) NOT NULL CHECK (weight > 0),
532 width_x_length_x_depth VARCHAR(20) NOT NULL,
533 aprox_production_time INTEGER NOT NULL,
534 description VARCHAR(500) NOT NULL,
535 category_id INTEGER NOT NULL REFERENCES category(id) ON DELETE SET DEFAULT
536 );
537
538 CREATE TABLE IF NOT EXISTS image (
539 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
540 image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
541 );
542
543 CREATE TABLE IF NOT EXISTS color (
544 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
545 color VARCHAR(50)
546 );
547
548 CREATE TABLE IF NOT EXISTS store (
549 store_ID VARCHAR(3) PRIMARY KEY,
550 name VARCHAR(50) UNIQUE NOT NULL,
551 date_of_founding DATE NOT NULL,
552 physical_address VARCHAR(100) NOT NULL,
553 store_email VARCHAR(40) UNIQUE NOT NULL CHECK (store_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
554 rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0)
555 );
556
557 CREATE TABLE IF NOT EXISTS personal (
558 id VARCHAR(10) PRIMARY KEY,
559 first_name VARCHAR(20) NOT NULL,
560 last_name VARCHAR(20) NOT NULL,
561 ssn VARCHAR(13) NOT NULL CHECK (ssn ~ '^[0-9]{13}$'),
562 email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
563 password VARCHAR NOT NULL
564 );
565
566 CREATE TABLE IF NOT EXISTS permissions (
567 personal_is VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
568 type VARCHAR(50) NOT NULL,
569 authorisation VARCHAR(50) NOT NULL
570 );
571
572 CREATE TABLE IF NOT EXISTS boss (
573 boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE
574 );
575
576 CREATE TABLE IF NOT EXISTS employees (
577 employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
578 date_of_hire DATE NOT NULL
579 );
580
581 CREATE TABLE IF NOT EXISTS client (
582 client_ID SERIAL PRIMARY KEY,
583 first_name VARCHAR(50) NOT NULL,
584 last_name VARCHAR(50) NOT NULL,
585 email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
586 password VARCHAR NOT NULL
587 );
588
589 CREATE TABLE IF NOT EXISTS delivery_address (
590 client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID) ON DELETE CASCADE,
591 address VARCHAR(200) NOT NULL,
592 city VARCHAR(30) NOT NULL,
593 postcode VARCHAR(20) NOT NULL,
594 country VARCHAR(40) NOT NULL,
595 is_default BOOLEAN DEFAULT TRUE
596 );
597
598 CREATE TABLE IF NOT EXISTS "order" (
599 order_num VARCHAR(11) PRIMARY KEY,
600 client_ID INTEGER REFERENCES client(client_ID) ON DELETE CASCADE,
601 status VARCHAR(20) NOT NULL DEFAULT 'placed order',
602 last_date_mod TIMESTAMP NOT NULL,
603 payment_method VARCHAR(250) NOT NULL,
604 discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<=100.00),
605 CONSTRAINT check_status CHECK (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled'))
606 );
607
608 CREATE TABLE IF NOT EXISTS review (
609 order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num) ON DELETE CASCADE,
610 comment VARCHAR(300),
611 rating DECIMAL(2,1) NOT NULL CHECK(rating>=0.0 AND rating<=5.0),
612 last_mod_date TIMESTAMP NOT NULL
613 );
614
615 CREATE TABLE IF NOT EXISTS refund (
616 refund_id SERIAL PRIMARY KEY,
617 order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE,
618 reason VARCHAR(300),
619 amount DECIMAL(5,2) NOT NULL,
620 status VARCHAR(100) NOT NULL DEFAULT 'requested refund',
621 CONSTRAINT check_refund_status CHECK (status IN ('requested refund', 'being revied', 'approved', 'not approved', 'processed'))
622 );
623
624 CREATE TABLE IF NOT EXISTS report (
625 date TIMESTAMP NOT NULL,
626 store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID) ON DELETE CASCADE,
627 overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0),
628 sales_trend VARCHAR(100) NOT NULL,
629 marketing_growth VARCHAR(100) NOT NULL,
630 owner_signature VARCHAR(50) NOT NULL DEFAULT 'Not signed yet',
631 PRIMARY KEY (date, store_ID)
632 );
633
634 CREATE TABLE IF NOT EXISTS monthly_profit (
635 report_date TIMESTAMP NOT NULL,
636 store_ID VARCHAR(3) NOT NULL,
637 month_and_year DATE NOT NULL,
638 profit NUMERIC NOT NULL DEFAULT 0.0,
639 PRIMARY KEY(report_date, store_ID),
640 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE
641 );
642
643 CREATE TABLE IF NOT EXISTS exchanges_data (
644 report_date TIMESTAMP NOT NULL,
645 store_ID VARCHAR(3) NOT NULL,
646 monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
647 date TIMESTAMP NOT NULL,
648 sales NUMERIC NOT NULL DEFAULT 0.0,
649 damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages<=0),
650 PRIMARY KEY (report_date, store_ID),
651 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE
652 );
653
654 CREATE TABLE IF NOT EXISTS request (
655 request_num VARCHAR(14) PRIMARY KEY,
656 date_and_time TIMESTAMP NOT NULL,
657 problem VARCHAR(300) NOT NULL,
658 notes_of_communication VARCHAR,
659 customer_satisfaction NUMERIC NOT NULL
660 );
661
662 CREATE TABLE IF NOT EXISTS makes_request (
663 client_ID INTEGER NOT NULL REFERENCES client(client_ID) ON DELETE CASCADE,
664 order_num VARCHAR(11) UNIQUE NOT NULL REFERENCES "order"(order_num) ON DELETE CASCADE,
665 PRIMARY KEY(client_ID, order_num)
666 );
667
668 CREATE TABLE IF NOT EXISTS answers (
669 request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE,
670 personal_id VARCHAR(10) NOT NULL REFERENCES personal(id) ON DELETE CASCADE,
671 PRIMARY KEY(request_num, personal_id)
672 );
673
674 CREATE TABLE IF NOT EXISTS for_store (
675 request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE,
676 store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE,
677 PRIMARY KEY(request_num, store_ID)
678 );
679
680 CREATE TABLE IF NOT EXISTS "change" (
681 date_and_time TIMESTAMP NOT NULL,
682 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
683 changes VARCHAR NOT NULL,
684 PRIMARY KEY (date_and_time, product_code)
685 );
686
687 CREATE TABLE IF NOT EXISTS makes_change (
688 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
689 change_date_time TIMESTAMP,
690 product_code VARCHAR(8),
691 PRIMARY KEY(personal_id, change_date_time, product_code),
692 FOREIGN KEY(change_date_time, product_code) REFERENCES "change"(date_and_time, product_code) ON DELETE CASCADE
693 );
694
695 CREATE TABLE IF NOT EXISTS works_in_store (
696 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
697 store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE,
698 PRIMARY KEY(personal_id, store_ID)
699 );
700
701 CREATE TABLE IF NOT EXISTS worked (
702 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
703 report_date TIMESTAMP,
704 store_ID VARCHAR(3),
705 wage NUMERIC NOT NULL CHECK (wage>=0),
706 pay_method VARCHAR DEFAULT 'full-time',
707 total_hours NUMERIC NOT NULL,
708 week VARCHAR(23) NOT NULL,
709 PRIMARY KEY (personal_id, report_date, store_ID),
710 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE,
711 CONSTRAINT check_pay_method CHECK (pay_method IN ('full_time', 'part-time', 'custom'))
712 );
713
714 CREATE TABLE IF NOT EXISTS sells (
715 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
716 store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE,
717 discount NUMERIC NOT NULL DEFAULT 0.0,
718 PRIMARY KEY (product_code, store_ID)
719 );
720
721 CREATE TABLE IF NOT EXISTS includes (
722 order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE,
723 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
724 quantity INTEGER NOT NULL CHECK(quantity>=0),
725 PRIMARY KEY (order_num, product_code)
726 );
727
728 CREATE TABLE IF NOT EXISTS approves (
729 boss_id VARCHAR(10) REFERENCES boss(boss_id) ON DELETE CASCADE,
730 report_date TIMESTAMP,
731 store_ID VARCHAR(3),
732 owner_signature VARCHAR NOT NULL,
733 PRIMARY KEY (boss_id, report_date, store_ID),
734 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE
735 );
736
737 -- These four small tables are application authentication/audit storage.
738 -- They do not modify any of the project tables above.
739 CREATE TABLE IF NOT EXISTS users (
740 id VARCHAR(50) PRIMARY KEY,
741 username VARCHAR(100) UNIQUE NOT NULL,
742 email VARCHAR(255) UNIQUE NOT NULL,
743 password VARCHAR(255) NOT NULL,
744 user_type VARCHAR(50) NOT NULL,
745 force_password_change BOOLEAN DEFAULT FALSE,
746 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
747 );
748
749 CREATE TABLE IF NOT EXISTS roles (
750 role_id SERIAL PRIMARY KEY,
751 name VARCHAR(50) UNIQUE NOT NULL,
752 description TEXT
753 );
754
755 CREATE TABLE IF NOT EXISTS user_roles (
756 user_id VARCHAR(50) REFERENCES users(id) ON DELETE CASCADE,
757 role_id INTEGER REFERENCES roles(role_id) ON DELETE CASCADE,
758 PRIMARY KEY(user_id, role_id)
759 );
760
761 CREATE TABLE IF NOT EXISTS audit_log (
762 log_id BIGSERIAL PRIMARY KEY,
763 user_id VARCHAR(50),
764 action VARCHAR(100) NOT NULL,
765 resource_type VARCHAR(50),
766 resource_id VARCHAR(50),
767 details TEXT,
768 ip_address VARCHAR(45),
769 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
770 );
771
772 CREATE INDEX IF NOT EXISTS idx_product_category ON product(category_id);
773 CREATE INDEX IF NOT EXISTS idx_order_client ON "order"(client_ID);
774 CREATE INDEX IF NOT EXISTS idx_review_order ON review(order_num);
775 CREATE INDEX IF NOT EXISTS idx_request_date ON request(date_and_time);
776 CREATE INDEX IF NOT EXISTS idx_refund_order ON refund(order_num);
777 CREATE INDEX IF NOT EXISTS idx_personal_email ON personal(email);
778 CREATE INDEX IF NOT EXISTS idx_client_email ON client(email);
779 CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
780 CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
781 CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id);
782 `;
783
784 await pool.query(schema);
785
786 const roles = [
787 ['admin', 'System administrator'],
788 ['store_owner', 'Store owner'],
789 ['store_employee', 'Store employee'],
790 ['client', 'Registered client'],
791 ['guest', 'Unregistered guest']
792 ];
793
794 for (const [name, description] of roles) {
795 await pool.query(
796 'INSERT INTO roles(name, description) VALUES($1,$2) ON CONFLICT(name) DO NOTHING',
797 [name, description]
798 );
799 }
800
801 const hash = bcrypt.hashSync('Admin123!', 10);
802 await pool.query(
803 `INSERT INTO users(id, username, email, password, user_type, force_password_change)
804 VALUES('000000','admin','admin@handcraft.com',$1,'admin',TRUE) ON CONFLICT(id) DO NOTHING`,
805 [hash]
806 );
807 await pool.query(
808 `INSERT INTO personal(id, first_name, last_name, ssn, email, password)
809 VALUES('000000','Admin','User','0000000000000','admin@handcraft.com',$1) ON CONFLICT(id) DO NOTHING`,
810 [hash]
811 );
812 await pool.query(`INSERT INTO boss(boss_id) VALUES('000000') ON CONFLICT(boss_id) DO NOTHING`);
813 await pool.query(
814 `INSERT INTO permissions(personal_is,type,authorisation)
815 VALUES('000000','ADMIN','full_access') ON CONFLICT(personal_is) DO NOTHING`
816 );
817 await pool.query(
818 `INSERT INTO user_roles(user_id,role_id)
819 SELECT '000000', role_id FROM roles WHERE name='admin' ON CONFLICT DO NOTHING`
820 );
821 console.log('✅ PostgreSQL project schema was recreated successfully');
822 },
823
824 close() {
825 return pool.end();
826 },
827
828 getUserById(id, callback) {
829 dbQuery(
830 `SELECT u.*,
831 COALESCE(json_agg(json_build_object('name',r.name,'description',r.description))
832 FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles
833 FROM users u
834 LEFT JOIN user_roles ur ON ur.user_id=u.id
835 LEFT JOIN roles r ON r.role_id=ur.role_id
836 WHERE u.id=$1
837 GROUP BY u.id`,
838 [String(id)],
839 (err, result) => callback(err, result?.rows?.[0])
840 );
841 },
842
843 getUserByUsername(username, callback) {
844 dbQuery(
845 `SELECT u.*,
846 COALESCE(json_agg(json_build_object('name',r.name,'description',r.description))
847 FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles
848 FROM users u
849 LEFT JOIN user_roles ur ON ur.user_id=u.id
850 LEFT JOIN roles r ON r.role_id=ur.role_id
851 WHERE u.username=$1 OR u.email=$1
852 GROUP BY u.id
853 LIMIT 1`,
854 [username],
855 (err, result) => callback(err, result?.rows?.[0])
856 );
857 },
858
859 createUser(id, username, email, password, userType, callback) {
860 dbQuery(
861 `INSERT INTO users(id,username,email,password,user_type,force_password_change)
862 VALUES($1,$2,$3,$4,$5,FALSE) RETURNING id`,
863 [String(id), username, email, password, userType],
864 (err, result) => {
865 if (err) return callback(err);
866 const roleName = userType === 'client' ? 'client' :
867 userType === 'store_owner' ? 'store_owner' :
868 userType === 'store_employee' ? 'store_employee' : 'guest';
869 dbQuery(
870 `INSERT INTO user_roles(user_id,role_id)
871 SELECT $1, role_id FROM roles WHERE name=$2`,
872 [String(id), roleName],
873 roleErr => callback(roleErr, String(id))
874 );
875 }
876 );
877 },
878
879 createClient(data, callback) {
880 dbQuery(
881 `INSERT INTO client(first_name,last_name,email,password)
882 VALUES($1,$2,$3,$4) RETURNING client_id`,
883 [data.firstName || data.first_name || '', data.lastName || data.last_name || '', data.email, data.password],
884 (err, result) => callback(err, result?.rows?.[0]?.client_id)
885 );
886 },
887
888 getClientByEmail(email, callback) {
889 dbQuery('SELECT * FROM client WHERE email=$1', [email],
890 (err, result) => callback(err, result?.rows?.[0]));
891 },
892
893 getClientById(id, callback) {
894 dbQuery('SELECT * FROM client WHERE client_id=$1', [id],
895 (err, result) => callback(err, result?.rows?.[0]));
896 },
897
898 getPersonalByEmail(email, callback) {
899 dbQuery('SELECT * FROM personal WHERE email=$1', [email],
900 (err, result) => callback(err, result?.rows?.[0]));
901 },
902
903 getPersonalById(id, callback) {
904 dbQuery('SELECT * FROM personal WHERE id=$1', [String(id)],
905 (err, result) => callback(err, result?.rows?.[0]));
906 },
907
908 verifyPassword(password, hash) {
909 try { return bcrypt.compareSync(password, hash); } catch { return false; }
910 },
911
912 verifyClientPassword(password, hash, callback) {
913 bcrypt.compare(password, hash, callback);
914 },
915
916 logAudit(userId, action, resourceType, resourceId, details, ipAddress) {
917 dbQuery(
918 `INSERT INTO audit_log(user_id,action,resource_type,resource_id,details,ip_address)
919 VALUES($1,$2,$3,$4,$5,$6)`,
920 [userId == null ? null : String(userId), action, resourceType,
921 resourceId == null ? null : String(resourceId), details, ipAddress],
922 () => {}
923 );
924 },
925
926 getProducts(categoryId, searchTerm, callback) {
927 const params = [];
928 const where = [];
929 if (categoryId) {
930 params.push(categoryId);
931 where.push(`p.category_id=$${params.length}`);
932 }
933 if (searchTerm) {
934 params.push(`%${searchTerm}%`);
935 where.push(`(p.description ILIKE $${params.length} OR p.code ILIKE $${params.length})`);
936 }
937 const sql = `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id
938 FROM product p
939 LEFT JOIN category c ON c.id=p.category_id
940 ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
941 ORDER BY p.code`;
942 dbQuery(sql, params, (err, result) => callback(err, result?.rows || []));
943 },
944
945 getProductById(id, callback) {
946 dbQuery(
947 `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id
948 FROM product p
949 LEFT JOIN category c ON c.id=p.category_id
950 WHERE p.code=$1 LIMIT 1`,
951 [String(id)],
952 (err,result)=>callback(err,result?.rows?.[0])
953 );
954 },
955
956 getProductByCode(code, callback) {
957 dbQuery(
958 `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id
959 FROM product p
960 LEFT JOIN category c ON c.id=p.category_id
961 WHERE p.code=$1`,
962 [code],
963 (err,result)=>callback(err,result?.rows?.[0])
964 );
965 },
966
967 addProduct(personalId, data, callback) {
968 const storeId = data.store_id || data.storeId || String(data.code || '').slice(0,3);
969 if (!data.category_id) {
970 return callback(new Error('category_id is required because product.category_id is NOT NULL'));
971 }
972 dbQuery(
973 `INSERT INTO product(code,price,availability,weight,width_x_length_x_depth,
974 aprox_production_time,description,category_id)
975 VALUES($1,$2,$3,$4,$5,$6,$7,$8) RETURNING code`,
976 [
977 data.code, data.price, data.availability ?? 0, data.weight,
978 data.width_x_length_x_depth || data.dimensions || '',
979 data.aprox_production_time ?? data.production_time ?? 0,
980 data.description, data.category_id
981 ],
982 (err,result)=>{
983 if (err) return callback(err);
984 dbQuery(
985 `INSERT INTO sells(product_code,store_ID,discount)
986 VALUES($1,$2,$3)
987 ON CONFLICT(product_code,store_ID)
988 DO UPDATE SET discount=EXCLUDED.discount`,
989 [data.code,storeId,data.discount || 0],
990 e => callback(e, data.code)
991 );
992 }
993 );
994 },
995
996 updateProduct(personalId, data, callback) {
997 const fields = [];
998 const params = [];
999 const allowed = [
1000 ['price','price'], ['availability','availability'], ['weight','weight'],
1001 ['width_x_length_x_depth','width_x_length_x_depth'],
1002 ['dimensions','width_x_length_x_depth'],
1003 ['aprox_production_time','aprox_production_time'],
1004 ['production_time','aprox_production_time'],
1005 ['description','description'], ['category_id','category_id']
1006 ];
1007 for (const [input,col] of allowed) {
1008 if (data[input] !== undefined) {
1009 params.push(data[input]);
1010 fields.push(`${col}=$${params.length}`);
1011 }
1012 }
1013 if (!fields.length) return callback(null,0);
1014 params.push(data.code);
1015 dbQuery(`UPDATE product SET ${fields.join(', ')} WHERE code=$${params.length}`, params,
1016 (err,result)=>callback(err,result?.rowCount || 0));
1017 },
1018
1019 deleteProduct(productCode, storeId, personalId, callback) {
1020 dbQuery('DELETE FROM product WHERE code=$1 AND LEFT(code,3)=$2', [productCode,storeId],
1021 (err)=>callback(err));
1022 },
1023
1024 createCategory(data, callback) {
1025 const parent = data.parent_category_id ?? data.parentCategoryId ?? data.parent_id;
1026 if (parent === undefined || parent === null || parent === '') {
1027 return callback(new Error('parent_category_id is required by the project schema'));
1028 }
1029 dbQuery(
1030 `INSERT INTO category(name,parent_category_id) VALUES($1,$2) RETURNING id,name,parent_category_id`,
1031 [data.name, parent],
1032 (err,result)=>callback(err,result?.rows?.[0])
1033 );
1034 },
1035
1036 getCategories(callback) {
1037 dbQuery('SELECT * FROM category ORDER BY name', [], (err,result)=>callback(err,result?.rows||[]));
1038 },
1039
1040 getCategoriesWithParents(callback) {
1041 dbQuery(
1042 `SELECT c.*,p.name AS parent_name
1043 FROM category c LEFT JOIN category p ON p.id=c.parent_category_id
1044 ORDER BY c.name`,
1045 [], (err,result)=>callback(err,result?.rows||[])
1046 );
1047 },
1048
1049 getStores(callback) {
1050 dbQuery('SELECT * FROM store ORDER BY name', [], (err,result)=>callback(err,result?.rows||[]));
1051 },
1052
1053 createOrderNew(data, callback) {
1054 const items = data.items || data.products || data.order_items || [];
1055 const storeId = data.store_id || data.storeId || (items[0]?.code ? String(items[0].code).slice(0,3) : null);
1056 if (!storeId) return callback(new Error('Store ID is required'));
1057 const year = String(new Date().getFullYear()).slice(-3);
1058
1059 dbQuery(
1060 `SELECT COUNT(*)::int AS n
1061 FROM "order"
1062 WHERE LEFT(order_num,3)=$1
1063 AND SUBSTRING(order_num FROM 4 FOR 3)=$2`,
1064 [storeId, year],
1065 (countErr,countResult)=>{
1066 if (countErr) return callback(countErr);
1067 const seq=Number(countResult.rows[0].n)+1;
1068 const orderNum=`${storeId}${year}${String(seq).padStart(5,'0')}`;
1069 dbQuery(
1070 `INSERT INTO "order"(order_num,client_ID,status,last_date_mod,payment_method,discount)
1071 VALUES($1,$2,$3,CURRENT_TIMESTAMP,$4,$5) RETURNING order_num`,
1072 [orderNum,data.client_id,data.status||'placed order',data.payment_method||'cash',data.discount||0],
1073 (err,result)=>{
1074 if(err) return callback(err);
1075 let pending=items.length;
1076 if(!pending) return callback(null,orderNum);
1077 let firstErr=null;
1078 for(const item of items){
1079 dbQuery(
1080 `INSERT INTO includes(order_num,product_code,quantity) VALUES($1,$2,$3)`,
1081 [orderNum,item.product_code||item.code,item.quantity||1],
1082 e=>{ if(e && !firstErr) firstErr=e; if(--pending===0) callback(firstErr,firstErr?undefined:orderNum); }
1083 );
1084 }
1085 }
1086 );
1087 }
1088 );
1089 },
1090
1091 getOrdersByClient(clientId, callback) {
1092 dbQuery(
1093 `SELECT o.*, LEFT(o.order_num,3) AS store_id,
1094 o.last_date_mod AS order_date,
1095 COALESCE(json_agg(json_build_object('product_code',i.product_code,'quantity',i.quantity,'price',p.price))
1096 FILTER(WHERE i.product_code IS NOT NULL),'[]') AS items
1097 FROM "order" o
1098 LEFT JOIN includes i ON i.order_num=o.order_num
1099 LEFT JOIN product p ON p.code=i.product_code
1100 WHERE o.client_ID=$1
1101 GROUP BY o.order_num
1102 ORDER BY o.last_date_mod DESC`,
1103 [clientId],(err,result)=>callback(err,result?.rows||[])
1104 );
1105 },
1106
1107 createReviewNew(data, callback) {
1108 dbQuery(
1109 `INSERT INTO review(order_num,comment,rating,last_mod_date)
1110 VALUES($1,$2,$3,CURRENT_TIMESTAMP)
1111 RETURNING order_num`,
1112 [data.order_num,data.comment||null,data.rating],
1113 (err,result)=>callback(err,result?.rows?.[0]?.order_num)
1114 );
1115 },
1116
1117 createRequest(data, callback) {
1118 dbQuery(
1119 `INSERT INTO request(request_num,date_and_time,problem,notes_of_communication,customer_satisfaction)
1120 VALUES($1,$2,$3,$4,0) RETURNING request_num`,
1121 [data.request_num,data.date_and_time,data.problem,data.notes_of_communication||null],
1122 (err,result)=>{
1123 if (err) return callback(err);
1124 dbQuery(
1125 `INSERT INTO for_store(request_num,store_ID) VALUES($1,$2)`,
1126 [data.request_num,data.store_id],
1127 storeErr=>{
1128 if (storeErr) return callback(storeErr);
1129 if (data.order_num) {
1130 dbQuery(
1131 `INSERT INTO makes_request(client_ID,order_num) VALUES($1,$2)`,
1132 [data.client_id,data.order_num],
1133 e=>callback(e,data.request_num)
1134 );
1135 } else {
1136 callback(null,data.request_num);
1137 }
1138 }
1139 );
1140 }
1141 );
1142 },
1143
1144 createRefund(data, callback) {
1145 const suppliedId = data.refund_id;
1146 const query = suppliedId
1147 ? `INSERT INTO refund(refund_id,order_num,reason,amount,status) VALUES($1,$2,$3,$4,$5) RETURNING refund_id`
1148 : `INSERT INTO refund(order_num,reason,amount,status) VALUES($1,$2,$3,$4) RETURNING refund_id`;
1149 const params = suppliedId
1150 ? [Number.parseInt(String(suppliedId),10) || undefined,data.order_num,data.reason||null,data.amount,data.status||'requested refund']
1151 : [data.order_num,data.reason||null,data.amount,data.status||'requested refund'];
1152 dbQuery(query,params,(err,result)=>callback(err,result?.rows?.[0]?.refund_id));
1153 },
1154
1155 getAllUsers(callback) {
1156 dbQuery(`SELECT u.*,COALESCE(json_agg(r.name) FILTER(WHERE r.role_id IS NOT NULL),'[]') roles
1157 FROM users u LEFT JOIN user_roles ur ON ur.user_id=u.id
1158 LEFT JOIN roles r ON r.role_id=ur.role_id GROUP BY u.id ORDER BY u.created_at DESC`,
1159 [],(err,result)=>callback(err,result?.rows||[]));
1160 },
1161
1162 getAllOrders(callback) {
1163 dbQuery(`SELECT o.*, LEFT(o.order_num,3) AS store_id, o.last_date_mod AS order_date,
1164 c.first_name,c.last_name,c.email
1165 FROM "order" o
1166 LEFT JOIN client c ON c.client_id=o.client_ID
1167 ORDER BY o.last_date_mod DESC`,
1168 [],(err,result)=>callback(err,result?.rows||[]));
1169 },
1170
1171 getStoreProducts(storeId, callback) {
1172 dbQuery(`SELECT p.*,c.name AS category_name,LEFT(p.code,3) AS store_id,s.discount
1173 FROM product p
1174 LEFT JOIN category c ON c.id=p.category_id
1175 LEFT JOIN sells s ON s.product_code=p.code AND s.store_ID=$1
1176 WHERE LEFT(p.code,3)=$1 OR EXISTS(SELECT 1 FROM sells sx WHERE sx.product_code=p.code AND sx.store_ID=$1)
1177 ORDER BY p.code`,[storeId],(err,result)=>callback(err,result?.rows||[]));
1178 },
1179
1180 getStoreOrders(storeId, callback) {
1181 dbQuery(`SELECT o.*,LEFT(o.order_num,3) AS store_id,o.last_date_mod AS order_date,c.first_name,c.last_name
1182 FROM "order" o
1183 LEFT JOIN client c ON c.client_id=o.client_ID
1184 WHERE LEFT(o.order_num,3)=$1 ORDER BY o.last_date_mod DESC`,[storeId],
1185 (err,result)=>callback(err,result?.rows||[]));
1186 },
1187
1188 getStoreEmployees(storeId, callback) {
1189 dbQuery(`SELECT p.*,e.date_of_hire,per.type,per.authorisation
1190 FROM personal p JOIN works_in_store w ON w.personal_id=p.id
1191 LEFT JOIN employees e ON e.employee_id=p.id
1192 LEFT JOIN permissions per ON per.personal_is=p.id
1193 WHERE w.store_ID=$1 ORDER BY p.last_name,p.first_name`,
1194 [storeId],(err,result)=>callback(err,result?.rows||[]));
1195 },
1196
1197 getStoreReports(storeId, callback) {
1198 dbQuery(`SELECT * FROM report WHERE store_ID=$1 ORDER BY date DESC`,[storeId],
1199 (err,result)=>callback(err,result?.rows||[]));
1200 },
1201
1202 getStoreStats(storeId, callback) {
1203 const sql=`SELECT
1204 (SELECT COUNT(*) FROM product WHERE LEFT(code,3)=$1)::int AS product_count,
1205 (SELECT COUNT(*) FROM "order" WHERE LEFT(order_num,3)=$1)::int AS order_count,
1206 (SELECT COALESCE(SUM(i.quantity*p.price*(1-o.discount/100)),0)
1207 FROM "order" o JOIN includes i ON i.order_num=o.order_num JOIN product p ON p.code=i.product_code
1208 WHERE LEFT(o.order_num,3)=$1) AS revenue,
1209 (SELECT COUNT(*) FROM works_in_store WHERE store_ID=$1)::int AS employee_count,
1210 (SELECT COUNT(*) FROM for_store WHERE store_ID=$1)::int AS request_count,
1211 (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE LEFT(o.order_num,3)=$1)::int AS refund_count`;
1212 dbQuery(sql,[storeId],(err,result)=>callback(err,result?.rows?.[0]||{}));
1213 },
1214
1215 getEmployeeTasks(personalId, storeId, callback) {
1216 dbQuery(`SELECT r.*,a.personal_id AS answered_by
1217 FROM request r
1218 JOIN for_store fs ON fs.request_num=r.request_num
1219 LEFT JOIN answers a ON a.request_num=r.request_num
1220 WHERE fs.store_ID=$1 AND (a.personal_id=$2 OR a.personal_id IS NULL)
1221 ORDER BY r.date_and_time DESC`,
1222 [storeId,personalId],(err,result)=>callback(err,result?.rows||[]));
1223 },
1224
1225 getClientStats(clientId, callback) {
1226 dbQuery(`SELECT
1227 (SELECT COUNT(*) FROM "order" WHERE client_ID=$1)::int AS order_count,
1228 (SELECT COUNT(*) FROM review r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS review_count,
1229 (SELECT COUNT(*) FROM makes_request WHERE client_ID=$1)::int AS request_count,
1230 (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS refund_count`,
1231 [clientId],(err,result)=>callback(err,result?.rows?.[0]||{}));
1232 }
1233};
1234
1235
1236
1237// PostgreSQL schema initialization.
1238// The schema is based on the supplied Pasted markdown.md. A few invalid PostgreSQL
1239// declarations in the original paste are corrected here (for example DECIMMAL,
1240// PARTIAL KEY, and composite-key column-name typos). The existing HTTP layer also
1241// uses only the project schema plus the four authentication/audit support tables.
1242(async () => {
1243 try {
1244 await database.initializeDatabase();
1245 console.log('✅ Database initialization completed');
1246 } catch (err) {
1247 console.error('❌ Database initialization failed:', err);
1248 process.exitCode = 1;
1249 }
1250})();
1251
1252const server = http.createServer((req, res) => {
1253 const parsedUrl = url.parse(req.url, true);
1254 const pathname = parsedUrl.pathname;
1255 const ipAddress = getClientIp(req);
1256
1257 console.log('Request:', req.method, pathname);
1258
1259 res.setHeader('Access-Control-Allow-Origin', '*');
1260 res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS');
1261 res.setHeader('Access-Control-Allow-Headers', 'Content-Type');
1262
1263 if (req.method === 'OPTIONS') {
1264 res.writeHead(200);
1265 res.end();
1266 return;
1267 }
1268
1269 if (pathname === '/' || pathname === '/index.html') {
1270 serveStaticFile(res, 'index.html', 'text/html');
1271 } else if (pathname === '/login.html') {
1272 serveStaticFile(res, 'login.html', 'text/html');
1273 } else if (pathname === '/register.html') {
1274 serveStaticFile(res, 'register.html', 'text/html');
1275 } else if (pathname === '/register-store.html') {
1276 serveStaticFile(res, 'register-store.html', 'text/html');
1277 } else if (pathname === '/dashboard.html') {
1278 const cookies = parseCookies(req);
1279 const sessionId = cookies.sessionId;
1280
1281 if (!sessionId || !sessions.has(sessionId)) {
1282 res.writeHead(302, { 'Location': '/login.html' });
1283 res.end();
1284 return;
1285 }
1286
1287 if (tempAdminSessions.has(sessionId)) {
1288 res.writeHead(302, { 'Location': '/change-password.html?forced=true' });
1289 res.end();
1290 return;
1291 }
1292
1293 serveStaticFile(res, 'dashboard.html', 'text/html');
1294 } else if (pathname === '/verify-email.html') {
1295 serveStaticFile(res, 'verify-email.html', 'text/html');
1296 } else if (pathname === '/verify-2fa.html') {
1297 serveStaticFile(res, 'verify-2fa.html', 'text/html');
1298 } else if (pathname === '/admin.html') {
1299 // Check if user is authenticated
1300 const cookies = parseCookies(req);
1301 const sessionId = cookies.sessionId;
1302
1303 if (!sessionId || !sessions.has(sessionId)) {
1304 res.writeHead(302, { 'Location': '/login.html' });
1305 res.end();
1306 return;
1307 }
1308
1309 // Get user from session
1310 const userId = sessions.get(sessionId);
1311
1312 // Check if this is the admin user
1313 if (userId !== '000000') {
1314 // Not admin, redirect to appropriate dashboard
1315 if (userId.startsWith('client_')) {
1316 res.writeHead(302, { 'Location': '/client-dashboard.html' });
1317 } else if (userId.startsWith('personal_')) {
1318 // Check if store owner or employee
1319 const personalId = userId.replace('personal_', '');
1320
1321 database.database.get(
1322 'SELECT boss_id FROM boss WHERE boss_id = $1',
1323 [personalId],
1324 (err, boss) => {
1325 if (boss) {
1326 res.writeHead(302, { 'Location': '/store-owner.html' });
1327 } else {
1328 res.writeHead(302, { 'Location': '/store-employee.html' });
1329 }
1330 res.end();
1331 }
1332 );
1333 return;
1334 } else {
1335 res.writeHead(302, { 'Location': '/dashboard.html' });
1336 }
1337 res.end();
1338 return;
1339 }
1340
1341 serveStaticFile(res, 'admin.html', 'text/html');
1342 } else if (pathname === '/store-owner.html') {
1343 serveStaticFile(res, 'store-owner.html', 'text/html');
1344 } else if (pathname === '/store-employee.html') {
1345 serveStaticFile(res, 'store-employee.html', 'text/html');
1346 } else if (pathname === '/client-dashboard.html') {
1347 serveStaticFile(res, 'client-dashboard.html', 'text/html');
1348 } else if (pathname === '/products.html') {
1349 serveStaticFile(res, 'products.html', 'text/html');
1350 } else if (pathname === '/product-detail.html') {
1351 serveStaticFile(res, 'product-detail.html', 'text/html');
1352 } else if (pathname === '/checkout.html') {
1353 serveStaticFile(res, 'checkout.html', 'text/html');
1354 } else if (pathname === '/orders.html') {
1355 serveStaticFile(res, 'orders.html', 'text/html');
1356 } else if (pathname === '/reviews.html') {
1357 serveStaticFile(res, 'reviews.html', 'text/html');
1358 } else if (pathname === '/change-password.html') {
1359 serveStaticFile(res, 'change-password.html', 'text/html');
1360 } else if (pathname === '/style.css') {
1361 serveStaticFile(res, 'style.css', 'text/css');
1362 } else if (pathname === '/script.js') {
1363 serveStaticFile(res, 'script.js', 'application/javascript');
1364 }
1365
1366 else if (pathname === '/api/register' && req.method === 'POST') {
1367 let body = '';
1368 req.on('data', chunk => {
1369 body += chunk.toString();
1370 });
1371 req.on('end', () => {
1372 const { username, email, password, userType, firstName, lastName } = JSON.parse(body);
1373
1374 if (!username || !email || !password || !userType) {
1375 res.writeHead(400, { 'Content-Type': 'application/json' });
1376 res.end(JSON.stringify({ success: false, message: 'All fields are required' }));
1377 return;
1378 }
1379
1380 if (!validateEmail(email)) {
1381 res.writeHead(400, { 'Content-Type': 'application/json' });
1382 res.end(JSON.stringify({ success: false, message: 'Email is not valid' }));
1383 return;
1384 }
1385
1386 if (!validatePassword(password)) {
1387 res.writeHead(400, { 'Content-Type': 'application/json' });
1388 res.end(JSON.stringify({
1389 success: false,
1390 message: 'Password must have at least 8 characters, including uppercase, lowercase, number and special character'
1391 }));
1392 return;
1393 }
1394
1395 database.getUserByUsername(username, (err, existingUser) => {
1396 if (err) {
1397 console.error('Error checking user:', err);
1398 res.writeHead(500, { 'Content-Type': 'application/json' });
1399 res.end(JSON.stringify({ success: false, message: 'Server error checking user' }));
1400 return;
1401 }
1402
1403 database.getClientByEmail(email, (err, existingClient) => {
1404 if (err) {
1405 console.error('Error checking client:', err);
1406 }
1407
1408 if (existingUser || existingClient) {
1409 res.writeHead(400, { 'Content-Type': 'application/json' });
1410 res.end(JSON.stringify({ success: false, message: 'Username or email is already in use' }));
1411 return;
1412 }
1413
1414 const verificationCode = generateVerificationCode();
1415
1416 const tempUserData = {
1417 username,
1418 email,
1419 password,
1420 timestamp: Date.now(),
1421 userType: userType,
1422 firstName: firstName || '',
1423 lastName: lastName || ''
1424 };
1425
1426 tempUsers.set(verificationCode, tempUserData);
1427 verificationCodes.set(email, { code: verificationCode, timestamp: Date.now() });
1428
1429 console.log(`⏰ Generated verification code for ${email}, expires in 30 seconds`);
1430
1431 sendVerificationEmail(email, verificationCode)
1432 .then(() => {
1433 console.log('✅ Verification email sent to:', email);
1434 database.logAudit(null, 'REGISTER_ATTEMPT', 'user', null, `Registration attempt for ${email} as ${userType}`, ipAddress);
1435 res.writeHead(200, { 'Content-Type': 'application/json' });
1436 res.end(JSON.stringify({
1437 success: true,
1438 message: 'Verification code sent to your email (expires in 30 seconds)',
1439 email: email
1440 }));
1441 })
1442 .catch(error => {
1443 console.error('Error sending email:', error.message);
1444 res.writeHead(200, { 'Content-Type': 'application/json' });
1445 res.end(JSON.stringify({
1446 success: true,
1447 message: 'Verification code generated (check console, expires in 30 seconds)',
1448 email: email,
1449 developmentCode: verificationCode
1450 }));
1451 });
1452 });
1453 });
1454 });
1455 }
1456
1457 else if (pathname === '/api/register-store' && req.method === 'POST') {
1458 let body = '';
1459 req.on('data', chunk => {
1460 body += chunk.toString();
1461 });
1462 req.on('end', () => {
1463 const formData = JSON.parse(body);
1464
1465 const requiredFields = [
1466 'ownerFirstName', 'ownerLastName', 'ownerSSN', 'ownerEmail',
1467 'storeName', 'storeAddress', 'storeEmail', 'storeFoundingDate',
1468 'password', 'confirmPassword', 'signature'
1469 ];
1470
1471 for (const field of requiredFields) {
1472 if (!formData[field]) {
1473 res.writeHead(400, { 'Content-Type': 'application/json' });
1474 res.end(JSON.stringify({
1475 success: false,
1476 message: `Field ${field} is required`
1477 }));
1478 return;
1479 }
1480 }
1481
1482 if (!/^\d{13}$/.test(formData.ownerSSN)) {
1483 res.writeHead(400, { 'Content-Type': 'application/json' });
1484 res.end(JSON.stringify({
1485 success: false,
1486 message: 'SSN must be exactly 13 digits'
1487 }));
1488 return;
1489 }
1490
1491 const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
1492
1493 if (!emailRegex.test(formData.ownerEmail)) {
1494 res.writeHead(400, { 'Content-Type': 'application/json' });
1495 res.end(JSON.stringify({
1496 success: false,
1497 message: 'Please enter a valid personal email address'
1498 }));
1499 return;
1500 }
1501
1502 if (!emailRegex.test(formData.storeEmail)) {
1503 res.writeHead(400, { 'Content-Type': 'application/json' });
1504 res.end(JSON.stringify({
1505 success: false,
1506 message: 'Please enter a valid store email address'
1507 }));
1508 return;
1509 }
1510
1511 if (formData.password !== formData.confirmPassword) {
1512 res.writeHead(400, { 'Content-Type': 'application/json' });
1513 res.end(JSON.stringify({
1514 success: false,
1515 message: 'Passwords do not match'
1516 }));
1517 return;
1518 }
1519
1520 if (!validatePassword(formData.password)) {
1521 res.writeHead(400, { 'Content-Type': 'application/json' });
1522 res.end(JSON.stringify({
1523 success: false,
1524 message: 'Password must have at least 8 characters, including uppercase, lowercase, number and special character'
1525 }));
1526 return;
1527 }
1528
1529 database.getPersonalByEmail(formData.ownerEmail, (err, existingPersonal) => {
1530 if (err) {
1531 console.error('Error checking personal:', err);
1532 res.writeHead(500, { 'Content-Type': 'application/json' });
1533 res.end(JSON.stringify({ success: false, message: 'Server error checking personal' }));
1534 return;
1535 }
1536
1537 if (existingPersonal) {
1538 res.writeHead(400, { 'Content-Type': 'application/json' });
1539 res.end(JSON.stringify({ success: false, message: 'Personal email is already registered' }));
1540 return;
1541 }
1542
1543 database.database.get(
1544 'SELECT store_id FROM store WHERE store_email = $1',
1545 [formData.storeEmail],
1546 (err, existingStore) => {
1547 if (err) {
1548 console.error('Error checking store:', err);
1549 res.writeHead(500, { 'Content-Type': 'application/json' });
1550 res.end(JSON.stringify({ success: false, message: 'Server error checking store' }));
1551 return;
1552 }
1553
1554 if (existingStore) {
1555 res.writeHead(400, { 'Content-Type': 'application/json' });
1556 res.end(JSON.stringify({ success: false, message: 'Store email is already registered' }));
1557 return;
1558 }
1559
1560 // Get the maximum store_id to determine the next store ID
1561 database.database.get(
1562 'SELECT MAX(store_id) as max_store_num FROM store',
1563 [],
1564 (err, result) => {
1565 if (err) {
1566 console.error('Error getting max store ID:', err);
1567 res.writeHead(500, { 'Content-Type': 'application/json' });
1568 res.end(JSON.stringify({ success: false, message: 'Server error generating store ID' }));
1569 return;
1570 }
1571
1572 // Next store number is max + 1, starting from 1 if no stores exist
1573 let nextStoreNumber = 1;
1574
1575 if (result && result.max_store_num) {
1576 // Extract numeric part from store_id (format: XXX)
1577 const maxNum = parseInt(result.max_store_num, 10);
1578 if (!isNaN(maxNum)) {
1579 nextStoreNumber = maxNum + 1;
1580 }
1581 }
1582
1583 if (nextStoreNumber > 999) {
1584 res.writeHead(400, { 'Content-Type': 'application/json' });
1585 res.end(JSON.stringify({ success: false, message: 'Maximum store limit reached (999)' }));
1586 return;
1587 }
1588
1589 // Store ID is padded to 3 digits (VARCHAR)
1590 const storeIdPadded = nextStoreNumber.toString().padStart(3, '0');
1591
1592 // Personal ID is storeId + '001' (as string for display)
1593 const personalId = storeIdPadded + '001';
1594
1595 const verificationCode = generateVerificationCode();
1596
1597 const tempStoreData = {
1598 personalId: personalId, // VARCHAR for personal table
1599 ownerFirstName: formData.ownerFirstName,
1600 ownerLastName: formData.ownerLastName,
1601 ownerSSN: formData.ownerSSN,
1602 ownerEmail: formData.ownerEmail,
1603 storeId: storeIdPadded, // VARCHAR for store table
1604 storeIdPadded: storeIdPadded,
1605 storeName: formData.storeName,
1606 storeAddress: formData.storeAddress,
1607 storeEmail: formData.storeEmail,
1608 storeFoundingDate: formData.storeFoundingDate,
1609 storeDescription: formData.storeDescription || '',
1610 password: formData.password,
1611 signature: formData.signature,
1612 timestamp: Date.now()
1613 };
1614
1615 tempStoreRegistrations.set(verificationCode, tempStoreData);
1616 verificationCodes.set(formData.ownerEmail, {
1617 code: verificationCode,
1618 timestamp: Date.now(),
1619 storeRegistration: true
1620 });
1621
1622 console.log(`⏰ Generated store registration verification code for ${formData.ownerEmail}, expires in 30 seconds`);
1623 console.log(`🏪 Store ID will be: ${storeIdPadded}`);
1624 console.log(`👤 Personal ID will be: ${personalId}`);
1625
1626 sendStoreRegistrationEmail(formData.ownerEmail, verificationCode, formData.storeName)
1627 .then(() => {
1628 console.log('✅ Store registration email sent to:', formData.ownerEmail);
1629 database.logAudit(null, 'STORE_REGISTER_ATTEMPT', 'store', null, `Store registration attempt: ${formData.storeName}`, ipAddress);
1630 res.writeHead(200, { 'Content-Type': 'application/json' });
1631 res.end(JSON.stringify({
1632 success: true,
1633 message: 'Verification code sent to your email (expires in 30 seconds)',
1634 email: formData.ownerEmail,
1635 storeName: formData.storeName
1636 }));
1637 })
1638 .catch(error => {
1639 console.error('Error sending store registration email:', error.message);
1640 res.writeHead(200, { 'Content-Type': 'application/json' });
1641 res.end(JSON.stringify({
1642 success: true,
1643 message: 'Verification code generated (check console, expires in 30 seconds)',
1644 email: formData.ownerEmail,
1645 storeName: formData.storeName,
1646 developmentCode: verificationCode
1647 }));
1648 });
1649 }
1650 );
1651 }
1652 );
1653 });
1654 });
1655 }
1656
1657 else if (pathname === '/api/client-register' && req.method === 'POST') {
1658 let body = '';
1659 req.on('data', chunk => {
1660 body += chunk.toString();
1661 });
1662 req.on('end', () => {
1663 const { firstName, lastName, email, password, address, city, postcode, country, isDefaultAddress } = JSON.parse(body);
1664
1665 if (!firstName || !lastName || !email || !password) {
1666 res.writeHead(400, { 'Content-Type': 'application/json' });
1667 res.end(JSON.stringify({ success: false, message: 'First name, last name, email and password are required' }));
1668 return;
1669 }
1670
1671 if (!validateEmail(email)) {
1672 res.writeHead(400, { 'Content-Type': 'application/json' });
1673 res.end(JSON.stringify({ success: false, message: 'Email is not valid' }));
1674 return;
1675 }
1676
1677 if (!validatePassword(password)) {
1678 res.writeHead(400, { 'Content-Type': 'application/json' });
1679 res.end(JSON.stringify({
1680 success: false,
1681 message: 'Password must have at least 8 characters, including uppercase, lowercase, number and special character'
1682 }));
1683 return;
1684 }
1685
1686 database.getClientByEmail(email, (err, existingClient) => {
1687 if (err) {
1688 console.error('Error checking client:', err);
1689 res.writeHead(500, { 'Content-Type': 'application/json' });
1690 res.end(JSON.stringify({ success: false, message: 'Server error checking client' }));
1691 return;
1692 }
1693
1694 if (existingClient) {
1695 res.writeHead(400, { 'Content-Type': 'application/json' });
1696 res.end(JSON.stringify({ success: false, message: 'Email is already registered' }));
1697 return;
1698 }
1699
1700 const verificationCode = generateVerificationCode();
1701
1702 const tempUserData = {
1703 username: `${firstName} ${lastName}`,
1704 email,
1705 password,
1706 timestamp: Date.now(),
1707 userType: 'client',
1708 firstName: firstName,
1709 lastName: lastName,
1710 address: address || null,
1711 city: city || null,
1712 postcode: postcode || null,
1713 country: country || null,
1714 isDefaultAddress: isDefaultAddress || false
1715 };
1716
1717 tempUsers.set(verificationCode, tempUserData);
1718 verificationCodes.set(email, { code: verificationCode, timestamp: Date.now() });
1719
1720 console.log(`⏰ Generated verification code for client ${email}, expires in 30 seconds`);
1721
1722 sendVerificationEmail(email, verificationCode)
1723 .then(() => {
1724 console.log('✅ Verification email sent to:', email);
1725 database.logAudit(null, 'CLIENT_REGISTER_ATTEMPT', 'client', null, `Client registration attempt for ${email}`, ipAddress);
1726 res.writeHead(200, { 'Content-Type': 'application/json' });
1727 res.end(JSON.stringify({
1728 success: true,
1729 message: 'Verification code sent to your email (expires in 30 seconds)',
1730 email: email
1731 }));
1732 })
1733 .catch(error => {
1734 console.error('Error sending email:', error.message);
1735 res.writeHead(200, { 'Content-Type': 'application/json' });
1736 res.end(JSON.stringify({
1737 success: true,
1738 message: 'Verification code generated (check console, expires in 30 seconds)',
1739 email: email,
1740 developmentCode: verificationCode
1741 }));
1742 });
1743 });
1744 });
1745 }
1746
1747 else if (pathname === '/api/resend-verification' && req.method === 'POST') {
1748 let body = '';
1749 req.on('data', chunk => {
1750 body += chunk.toString();
1751 });
1752 req.on('end', () => {
1753 const { email } = JSON.parse(body);
1754
1755 if (!email) {
1756 res.writeHead(400, { 'Content-Type': 'application/json' });
1757 res.end(JSON.stringify({ success: false, message: 'Email is required' }));
1758 return;
1759 }
1760
1761 const existingTempUser = Array.from(tempUsers.values()).find(user => user.email === email);
1762
1763 if (existingTempUser) {
1764 const newVerificationCode = generateVerificationCode();
1765
1766 const tempUserData = {
1767 username: existingTempUser.username,
1768 email: existingTempUser.email,
1769 password: existingTempUser.password,
1770 timestamp: Date.now(),
1771 userType: existingTempUser.userType,
1772 firstName: existingTempUser.firstName || '',
1773 lastName: existingTempUser.lastName || '',
1774 address: existingTempUser.address || null,
1775 city: existingTempUser.city || null,
1776 postcode: existingTempUser.postcode || null,
1777 country: existingTempUser.country || null,
1778 isDefaultAddress: existingTempUser.isDefaultAddress || false
1779 };
1780
1781 tempUsers.forEach((value, key) => {
1782 if (value.email === email) {
1783 tempUsers.delete(key);
1784 }
1785 });
1786
1787 tempUsers.set(newVerificationCode, tempUserData);
1788 verificationCodes.set(email, { code: newVerificationCode, timestamp: Date.now() });
1789
1790 console.log(`🔄 Resent verification code for ${email}, expires in 30 seconds`);
1791
1792 sendVerificationEmail(email, newVerificationCode)
1793 .then(() => {
1794 res.writeHead(200, { 'Content-Type': 'application/json' });
1795 res.end(JSON.stringify({
1796 success: true,
1797 message: 'New verification code sent to your email (expires in 30 seconds)',
1798 email: email
1799 }));
1800 })
1801 .catch(error => {
1802 console.error('Error sending email:', error.message);
1803 res.writeHead(200, { 'Content-Type': 'application/json' });
1804 res.end(JSON.stringify({
1805 success: true,
1806 message: 'New verification code generated (check console, expires in 30 seconds)',
1807 email: email,
1808 developmentCode: newVerificationCode
1809 }));
1810 });
1811
1812 return;
1813 }
1814
1815 const existingTempStore = Array.from(tempStoreRegistrations.values()).find(store => store.ownerEmail === email);
1816
1817 if (existingTempStore) {
1818 const newVerificationCode = generateVerificationCode();
1819
1820 const tempStoreData = {
1821 personalId: existingTempStore.personalId,
1822 ownerFirstName: existingTempStore.ownerFirstName,
1823 ownerLastName: existingTempStore.ownerLastName,
1824 ownerSSN: existingTempStore.ownerSSN,
1825 ownerEmail: existingTempStore.ownerEmail,
1826 storeId: existingTempStore.storeId,
1827 storeIdPadded: existingTempStore.storeIdPadded,
1828 storeName: existingTempStore.storeName,
1829 storeAddress: existingTempStore.storeAddress,
1830 storeEmail: existingTempStore.storeEmail,
1831 storeFoundingDate: existingTempStore.storeFoundingDate,
1832 storeDescription: existingTempStore.storeDescription,
1833 password: existingTempStore.password,
1834 signature: existingTempStore.signature,
1835 timestamp: Date.now()
1836 };
1837
1838 tempStoreRegistrations.forEach((value, key) => {
1839 if (value.ownerEmail === email) {
1840 tempStoreRegistrations.delete(key);
1841 }
1842 });
1843
1844 tempStoreRegistrations.set(newVerificationCode, tempStoreData);
1845 verificationCodes.set(email, {
1846 code: newVerificationCode,
1847 timestamp: Date.now(),
1848 storeRegistration: true
1849 });
1850
1851 console.log(`🔄 Resent store registration verification code for ${email}, expires in 30 seconds`);
1852
1853 sendStoreRegistrationEmail(email, newVerificationCode, existingTempStore.storeName)
1854 .then(() => {
1855 res.writeHead(200, { 'Content-Type': 'application/json' });
1856 res.end(JSON.stringify({
1857 success: true,
1858 message: 'New verification code sent to your email (expires in 30 seconds)',
1859 email: email
1860 }));
1861 })
1862 .catch(error => {
1863 console.error('Error sending store registration email:', error.message);
1864 res.writeHead(200, { 'Content-Type': 'application/json' });
1865 res.end(JSON.stringify({
1866 success: true,
1867 message: 'New verification code generated (check console, expires in 30 seconds)',
1868 email: email,
1869 developmentCode: newVerificationCode
1870 }));
1871 });
1872
1873 return;
1874 }
1875
1876 res.writeHead(400, { 'Content-Type': 'application/json' });
1877 res.end(JSON.stringify({ success: false, message: 'No pending registration found for this email' }));
1878 });
1879 }
1880
1881 else if (pathname === '/api/verify-email' && req.method === 'POST') {
1882 let body = '';
1883 req.on('data', chunk => {
1884 body += chunk.toString();
1885 });
1886 req.on('end', () => {
1887 const { email, code } = JSON.parse(body);
1888
1889 if (!email || !code) {
1890 res.writeHead(400, { 'Content-Type': 'application/json' });
1891 res.end(JSON.stringify({ success: false, message: 'All fields are required' }));
1892 return;
1893 }
1894
1895 const verificationData = verificationCodes.get(email);
1896
1897 if (verificationData && verificationData.storeRegistration) {
1898 const tempStoreData = tempStoreRegistrations.get(code);
1899
1900 if (!tempStoreData || tempStoreData.ownerEmail !== email) {
1901 res.writeHead(400, { 'Content-Type': 'application/json' });
1902 res.end(JSON.stringify({ success: false, message: 'Invalid verification code' }));
1903 return;
1904 }
1905
1906 if (Date.now() - tempStoreData.timestamp > 30 * 1000) {
1907 tempStoreRegistrations.delete(code);
1908 verificationCodes.delete(email);
1909 res.writeHead(400, { 'Content-Type': 'application/json' });
1910 res.end(JSON.stringify({ success: false, message: 'Verification code has expired. Please request a new one.' }));
1911 return;
1912 }
1913
1914 database.database.run('BEGIN TRANSACTION', (err) => {
1915 if (err) {
1916 console.error('Error beginning transaction:', err);
1917 res.writeHead(500, { 'Content-Type': 'application/json' });
1918 res.end(JSON.stringify({ success: false, message: 'Server error during registration' }));
1919 return;
1920 }
1921
1922 // Insert into store table (store_id is VARCHAR)
1923 database.database.run(
1924 'INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES ($1, $2, $3, $4, $5, $6)',
1925 [
1926 tempStoreData.storeId,
1927 tempStoreData.storeName,
1928 tempStoreData.storeFoundingDate,
1929 tempStoreData.storeAddress,
1930 tempStoreData.storeEmail,
1931 0.0
1932 ],
1933 function(err) {
1934 if (err) {
1935 database.database.run('ROLLBACK');
1936 console.error('Error inserting store:', err);
1937 res.writeHead(400, { 'Content-Type': 'application/json' });
1938 res.end(JSON.stringify({ success: false, message: 'Error registering store' }));
1939 return;
1940 }
1941
1942 // Insert into personal table (id is VARCHAR)
1943 database.database.run(
1944 'INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES ($1, $2, $3, $4, $5, $6)',
1945 [
1946 tempStoreData.personalId,
1947 tempStoreData.ownerFirstName,
1948 tempStoreData.ownerLastName,
1949 tempStoreData.ownerSSN,
1950 tempStoreData.ownerEmail,
1951 bcrypt.hashSync(tempStoreData.password, 10)
1952 ],
1953 function(err) {
1954 if (err) {
1955 database.database.run('ROLLBACK');
1956 console.error('Error inserting personal:', err);
1957
1958 if (err.code === '23505') {
1959 res.writeHead(400, { 'Content-Type': 'application/json' });
1960 res.end(JSON.stringify({
1961 success: false,
1962 message: 'This personal ID is already taken. Please try again.'
1963 }));
1964 } else {
1965 res.writeHead(400, { 'Content-Type': 'application/json' });
1966 res.end(JSON.stringify({ success: false, message: 'Error registering personal information' }));
1967 }
1968 return;
1969 }
1970
1971 // Insert into boss table (boss_id is VARCHAR, references personal.id)
1972 database.database.run(
1973 'INSERT INTO boss (boss_id) VALUES ($1)',
1974 [tempStoreData.personalId],
1975 (err) => {
1976 if (err) {
1977 database.database.run('ROLLBACK');
1978 console.error('Error inserting boss:', err);
1979 res.writeHead(400, { 'Content-Type': 'application/json' });
1980 res.end(JSON.stringify({ success: false, message: 'Error registering as boss' }));
1981 return;
1982 }
1983
1984 // Insert into works_in_store table (personal_id is VARCHAR, store_id is VARCHAR)
1985 database.database.run(
1986 'INSERT INTO works_in_store (personal_id, store_id) VALUES ($1, $2)',
1987 [tempStoreData.personalId, tempStoreData.storeId],
1988 (err) => {
1989 if (err) {
1990 database.database.run('ROLLBACK');
1991 console.error('Error inserting works_in_store:', err);
1992 res.writeHead(400, { 'Content-Type': 'application/json' });
1993 res.end(JSON.stringify({ success: false, message: 'Error assigning to store' }));
1994 return;
1995 }
1996
1997 // Insert into permissions table (personal_id is VARCHAR)
1998 database.database.run(
1999 'INSERT INTO permissions (personal_id, type, authorisation) VALUES ($1, $2, $3)',
2000 [tempStoreData.personalId, 'BOSS', 'full_access'],
2001 (err) => {
2002 if (err) {
2003 console.error('Error inserting permissions:', err);
2004 }
2005
2006 // Also create entry in users table for login with force_password_change = 1
2007 database.database.run(
2008 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ($1, $2, $3, $4, $5, $6)',
2009 [
2010 tempStoreData.personalId,
2011 `${tempStoreData.ownerFirstName} ${tempStoreData.ownerLastName}`,
2012 tempStoreData.ownerEmail,
2013 bcrypt.hashSync(tempStoreData.password, 10),
2014 'store_owner',
2015 1
2016 ],
2017 (err) => {
2018 if (err) {
2019 console.error('Error creating user entry for store owner:', err);
2020 }
2021
2022 database.database.run('COMMIT', (commitErr) => {
2023 if (commitErr) {
2024 console.error('Error committing transaction:', commitErr);
2025 database.database.run('ROLLBACK');
2026 res.writeHead(500, { 'Content-Type': 'application/json' });
2027 res.end(JSON.stringify({ success: false, message: 'Error completing registration' }));
2028 return;
2029 }
2030
2031 tempStoreRegistrations.delete(code);
2032 verificationCodes.delete(email);
2033
2034 console.log(`✅ Store registration completed successfully:`);
2035 console.log(` Store ID: ${tempStoreData.storeId}`);
2036 console.log(` Store Name: ${tempStoreData.storeName}`);
2037 console.log(` Personal ID: ${tempStoreData.personalId}`);
2038 console.log(` Owner: ${tempStoreData.ownerFirstName} ${tempStoreData.ownerLastName}`);
2039
2040 database.logAudit(tempStoreData.personalId, 'STORE_REGISTER_SUCCESS', 'store', tempStoreData.storeId, `Store registered: ${tempStoreData.storeName}`, ipAddress);
2041
2042 res.writeHead(200, { 'Content-Type': 'application/json' });
2043 res.end(JSON.stringify({
2044 success: true,
2045 message: 'Store registration successful! You can now login.',
2046 storeId: tempStoreData.storeId,
2047 storeIdPadded: tempStoreData.storeIdPadded,
2048 storeName: tempStoreData.storeName,
2049 personalId: tempStoreData.personalId,
2050 userType: 'store_owner',
2051 redirectTo: 'login.html'
2052 }));
2053 });
2054 }
2055 );
2056 }
2057 );
2058 }
2059 );
2060 }
2061 );
2062 }
2063 );
2064 }
2065 );
2066 });
2067
2068 return;
2069 }
2070
2071 const tempUserData = tempUsers.get(code);
2072
2073 if (!tempUserData || tempUserData.email !== email) {
2074 res.writeHead(400, { 'Content-Type': 'application/json' });
2075 res.end(JSON.stringify({ success: false, message: 'Invalid verification code' }));
2076 return;
2077 }
2078
2079 if (Date.now() - tempUserData.timestamp > 30 * 1000) {
2080 tempUsers.delete(code);
2081 verificationCodes.delete(email);
2082 res.writeHead(400, { 'Content-Type': 'application/json' });
2083 res.end(JSON.stringify({ success: false, message: 'Verification code has expired. Please request a new one.' }));
2084 return;
2085 }
2086
2087 if (tempUserData.userType === 'client') {
2088 database.createClient({
2089 first_name: tempUserData.firstName || tempUserData.username.split(' ')[0] || '',
2090 last_name: tempUserData.lastName || tempUserData.username.split(' ')[1] || '',
2091 email: tempUserData.email,
2092 password: tempUserData.password
2093 }, (err, clientId) => {
2094 if (err) {
2095 console.error('Error creating client:', err);
2096 res.writeHead(400, { 'Content-Type': 'application/json' });
2097 res.end(JSON.stringify({ success: false, message: 'Registration failed' }));
2098 } else {
2099 if (tempUserData.address && tempUserData.city && tempUserData.postcode && tempUserData.country) {
2100 database.database.run(
2101 'INSERT INTO delivery_address (client_id, address, city, postcode, country, is_default) VALUES ($1, $2, $3, $4, $5, $6)',
2102 [
2103 clientId,
2104 tempUserData.address,
2105 tempUserData.city,
2106 tempUserData.postcode,
2107 tempUserData.country,
2108 tempUserData.isDefaultAddress ? 1 : 0
2109 ],
2110 (err) => {
2111 if (err) {
2112 console.error('Error saving delivery address:', err);
2113 }
2114 }
2115 );
2116 }
2117
2118 tempUsers.delete(code);
2119 verificationCodes.delete(email);
2120
2121 database.logAudit(clientId, 'REGISTER_SUCCESS', 'client', clientId.toString(), 'Client registered', ipAddress);
2122
2123 res.writeHead(200, { 'Content-Type': 'application/json' });
2124 res.end(JSON.stringify({
2125 success: true,
2126 message: 'Successfully registered! You can now login.',
2127 userId: clientId,
2128 userType: 'client',
2129 redirectTo: 'login.html'
2130 }));
2131 }
2132 });
2133 } else {
2134 const userId = 'user_' + Date.now().toString().slice(-8);
2135
2136 database.createUser(userId, tempUserData.username, tempUserData.email, tempUserData.password, tempUserData.userType, (err, userId) => {
2137 if (err) {
2138 console.error('Error creating user:', err);
2139 res.writeHead(400, { 'Content-Type': 'application/json' });
2140 res.end(JSON.stringify({ success: false, message: 'Registration failed' }));
2141 } else {
2142 tempUsers.delete(code);
2143 verificationCodes.delete(email);
2144
2145 database.logAudit(userId, 'REGISTER_SUCCESS', 'user', userId.toString(), `User registered as ${tempUserData.userType}`, ipAddress);
2146
2147 res.writeHead(200, { 'Content-Type': 'application/json' });
2148 res.end(JSON.stringify({
2149 success: true,
2150 message: 'Successfully registered! You can now login.',
2151 userId: userId,
2152 userType: tempUserData.userType,
2153 redirectTo: 'login.html'
2154 }));
2155 }
2156 });
2157 }
2158 });
2159 }
2160
2161 else if (pathname === '/api/login' && req.method === 'POST') {
2162 let body = '';
2163 req.on('data', chunk => {
2164 body += chunk.toString();
2165 });
2166 req.on('end', () => {
2167 const { email, password } = JSON.parse(body);
2168
2169 console.log(`🔍 Login attempt for email: ${email}`);
2170
2171 // First check if it's the admin user (special case)
2172 if (email === 'admin@handcraft.com') {
2173 database.getUserByUsername('admin', (err, adminUser) => {
2174 if (err || !adminUser) {
2175 console.error('Admin user not found');
2176 database.logAudit(null, 'LOGIN_FAILED', 'auth', null, `Admin login failed - user not found`, ipAddress);
2177 res.writeHead(401, { 'Content-Type': 'application/json' });
2178 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2179 return;
2180 }
2181
2182 if (database.verifyPassword(password, adminUser.password)) {
2183 const isFirstTimeLogin = adminUser.force_password_change === 1;
2184
2185 const twoFACode = generateVerificationCode();
2186 verificationCodes.set(adminUser.email, {
2187 code: twoFACode,
2188 timestamp: Date.now(),
2189 userId: adminUser.id,
2190 isFirstTimeLogin: isFirstTimeLogin,
2191 userType: 'admin',
2192 needsPasswordChange: isFirstTimeLogin
2193 });
2194
2195 console.log(`⏰ Generated 2FA code for admin ${adminUser.email}`);
2196
2197 send2FACode(adminUser.email, twoFACode)
2198 .then(() => {
2199 res.writeHead(200, { 'Content-Type': 'application/json' });
2200 res.end(JSON.stringify({
2201 success: true,
2202 message: 'Two-factor authentication code sent to your email',
2203 requires2FA: true,
2204 email: adminUser.email,
2205 username: adminUser.username,
2206 isFirstTimeLogin: isFirstTimeLogin,
2207 userType: 'admin'
2208 }));
2209 })
2210 .catch(error => {
2211 console.error('Error sending 2FA email:', error);
2212 res.writeHead(200, { 'Content-Type': 'application/json' });
2213 res.end(JSON.stringify({
2214 success: true,
2215 message: 'Two-factor authentication required',
2216 requires2FA: true,
2217 email: adminUser.email,
2218 username: adminUser.username,
2219 isFirstTimeLogin: isFirstTimeLogin,
2220 userType: 'admin',
2221 developmentCode: twoFACode
2222 }));
2223 });
2224 } else {
2225 database.logAudit(adminUser.id, 'LOGIN_FAILED', 'auth', adminUser.id.toString(), 'Invalid password for admin', ipAddress);
2226 res.writeHead(401, { 'Content-Type': 'application/json' });
2227 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2228 }
2229 });
2230
2231 return;
2232 }
2233
2234 // First check if it's a client
2235 database.getClientByEmail(email, (err, client) => {
2236 if (err) {
2237 console.error('Error checking client:', err);
2238 }
2239
2240 if (client) {
2241 console.log(`🔍 Found client: ${client.email}`);
2242
2243 if (!client.password) {
2244 console.log('❌ Client has no password set');
2245 database.logAudit(client.client_ID, 'LOGIN_FAILED', 'auth', client.client_ID?.toString() || 'unknown', 'Client has no password', ipAddress);
2246 res.writeHead(401, { 'Content-Type': 'application/json' });
2247 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2248 return;
2249 }
2250
2251 database.verifyClientPassword(password, client.password, (err, isValid) => {
2252 if (err || !isValid) {
2253 const clientId = client.client_ID || 'unknown';
2254 database.logAudit(clientId, 'LOGIN_FAILED', 'auth',
2255 typeof clientId === 'string' ? clientId : String(clientId),
2256 'Invalid password for client', ipAddress);
2257 res.writeHead(401, { 'Content-Type': 'application/json' });
2258 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2259 return;
2260 }
2261
2262 // Clients go directly to dashboard (no 2FA)
2263 const sessionId = generateSessionId();
2264 const clientId = client.client_ID;
2265 sessions.set(sessionId, `client_${clientId}`);
2266
2267 console.log(`✅ Client login successful. Session: ${sessionId}, User: client_${clientId}`);
2268
2269 database.logAudit(clientId, 'LOGIN_SUCCESS', 'auth',
2270 typeof clientId === 'string' ? clientId : String(clientId),
2271 'Client logged in successfully', ipAddress);
2272
2273 res.writeHead(200, {
2274 'Content-Type': 'application/json',
2275 'Set-Cookie': `sessionId=${sessionId}; HttpOnly; Path=/; Max-Age=3600; SameSite=Strict`
2276 });
2277
2278 res.end(JSON.stringify({
2279 success: true,
2280 message: 'Successfully logged in',
2281 user: {
2282 id: clientId,
2283 firstName: client.first_name,
2284 lastName: client.last_name,
2285 email: client.email,
2286 userType: 'client'
2287 },
2288 redirectTo: 'client-dashboard.html'
2289 }));
2290 });
2291
2292 return;
2293 }
2294
2295 // If not client, check personal table
2296 database.getPersonalByEmail(email, (err, personal) => {
2297 if (err) {
2298 console.error('Error checking personal:', err);
2299 }
2300
2301 if (personal) {
2302 console.log(`🔍 Found personal user: ${personal.email}`);
2303
2304 if (!personal.password) {
2305 console.log('❌ Personal has no password set');
2306 database.logAudit(personal.id, 'LOGIN_FAILED', 'auth', personal.id, 'Personal has no password', ipAddress);
2307 res.writeHead(401, { 'Content-Type': 'application/json' });
2308 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2309 return;
2310 }
2311
2312 database.verifyClientPassword(password, personal.password, (err, isValid) => {
2313 if (err || !isValid) {
2314 database.logAudit(personal.id, 'LOGIN_FAILED', 'auth', personal.id, 'Invalid password for personal', ipAddress);
2315 res.writeHead(401, { 'Content-Type': 'application/json' });
2316 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2317 return;
2318 }
2319
2320 // Check if this is a boss (store owner)
2321 database.database.get(
2322 'SELECT boss_id FROM boss WHERE boss_id = $1',
2323 [personal.id],
2324 (err, boss) => {
2325 if (err) {
2326 console.error('Error checking boss status:', err);
2327 }
2328
2329 if (boss) {
2330 // This is a store owner
2331 // Check if first time login from users table
2332 database.database.get(
2333 'SELECT force_password_change FROM users WHERE email = $1',
2334 [email],
2335 (err, user) => {
2336 const isFirstTimeLogin = user && user.force_password_change === 1;
2337
2338 const twoFACode = generateVerificationCode();
2339 verificationCodes.set(personal.email, {
2340 code: twoFACode,
2341 timestamp: Date.now(),
2342 userId: personal.id,
2343 isFirstTimeLogin: isFirstTimeLogin,
2344 userType: 'store_owner',
2345 needsPasswordChange: isFirstTimeLogin
2346 });
2347
2348 console.log(`⏰ Generated 2FA code for store owner ${personal.email}`);
2349
2350 send2FACode(personal.email, twoFACode)
2351 .then(() => {
2352 res.writeHead(200, { 'Content-Type': 'application/json' });
2353 res.end(JSON.stringify({
2354 success: true,
2355 message: 'Two-factor authentication code sent to your email',
2356 requires2FA: true,
2357 email: personal.email,
2358 isFirstTimeLogin: isFirstTimeLogin,
2359 userType: 'store_owner'
2360 }));
2361 })
2362 .catch(error => {
2363 console.error('Error sending 2FA email:', error);
2364 res.writeHead(200, { 'Content-Type': 'application/json' });
2365 res.end(JSON.stringify({
2366 success: true,
2367 message: 'Two-factor authentication required',
2368 requires2FA: true,
2369 email: personal.email,
2370 isFirstTimeLogin: isFirstTimeLogin,
2371 userType: 'store_owner',
2372 developmentCode: twoFACode
2373 }));
2374 });
2375 }
2376 );
2377
2378 return;
2379 }
2380
2381 // Check if this is an employee
2382 database.database.get(
2383 'SELECT employee_id FROM employees WHERE employee_id = $1',
2384 [personal.id],
2385 (err, employee) => {
2386 if (err) {
2387 console.error('Error checking employee status:', err);
2388 }
2389
2390 if (employee) {
2391 // This is an employee
2392 database.database.get(
2393 'SELECT force_password_change FROM users WHERE email = $1',
2394 [email],
2395 (err, user) => {
2396 const isFirstTimeLogin = user && user.force_password_change === 1;
2397
2398 const twoFACode = generateVerificationCode();
2399 verificationCodes.set(personal.email, {
2400 code: twoFACode,
2401 timestamp: Date.now(),
2402 userId: personal.id,
2403 isFirstTimeLogin: isFirstTimeLogin,
2404 userType: 'store_employee',
2405 needsPasswordChange: isFirstTimeLogin
2406 });
2407
2408 console.log(`⏰ Generated 2FA code for employee ${personal.email}`);
2409
2410 send2FACode(personal.email, twoFACode)
2411 .then(() => {
2412 res.writeHead(200, { 'Content-Type': 'application/json' });
2413 res.end(JSON.stringify({
2414 success: true,
2415 message: 'Two-factor authentication code sent to your email',
2416 requires2FA: true,
2417 email: personal.email,
2418 isFirstTimeLogin: isFirstTimeLogin,
2419 userType: 'store_employee'
2420 }));
2421 })
2422 .catch(error => {
2423 console.error('Error sending 2FA email:', error);
2424 res.writeHead(200, { 'Content-Type': 'application/json' });
2425 res.end(JSON.stringify({
2426 success: true,
2427 message: 'Two-factor authentication required',
2428 requires2FA: true,
2429 email: personal.email,
2430 isFirstTimeLogin: isFirstTimeLogin,
2431 userType: 'store_employee',
2432 developmentCode: twoFACode
2433 }));
2434 });
2435 }
2436 );
2437
2438 return;
2439 }
2440
2441 // If we get here, it's a personal record without boss/employee status
2442 // Treat as regular user
2443 database.database.get(
2444 'SELECT * FROM users WHERE email = $1',
2445 [email],
2446 (err, user) => {
2447 if (err || !user) {
2448 database.getUserByUsername(email, (err, userByUsername) => {
2449 if (err || !userByUsername) {
2450 database.logAudit(null, 'LOGIN_FAILED', 'auth', null, `Failed login attempt for email: ${email}`, ipAddress);
2451 res.writeHead(401, { 'Content-Type': 'application/json' });
2452 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2453 return;
2454 }
2455
2456 if (database.verifyPassword(password, userByUsername.password)) {
2457 const isFirstTimeLogin = userByUsername.force_password_change === 1;
2458
2459 const twoFACode = generateVerificationCode();
2460 verificationCodes.set(userByUsername.email, {
2461 code: twoFACode,
2462 timestamp: Date.now(),
2463 userId: userByUsername.id,
2464 isFirstTimeLogin: isFirstTimeLogin,
2465 userType: userByUsername.user_type,
2466 needsPasswordChange: isFirstTimeLogin
2467 });
2468
2469 send2FACode(userByUsername.email, twoFACode)
2470 .then(() => {
2471 res.writeHead(200, { 'Content-Type': 'application/json' });
2472 res.end(JSON.stringify({
2473 success: true,
2474 message: 'Two-factor authentication code sent to your email',
2475 requires2FA: true,
2476 email: userByUsername.email,
2477 username: userByUsername.username,
2478 isFirstTimeLogin: isFirstTimeLogin,
2479 userType: userByUsername.user_type
2480 }));
2481 })
2482 .catch(error => {
2483 console.error('Error sending 2FA email:', error);
2484 res.writeHead(200, { 'Content-Type': 'application/json' });
2485 res.end(JSON.stringify({
2486 success: true,
2487 message: 'Two-factor authentication required',
2488 requires2FA: true,
2489 email: userByUsername.email,
2490 username: userByUsername.username,
2491 isFirstTimeLogin: isFirstTimeLogin,
2492 userType: userByUsername.user_type,
2493 developmentCode: twoFACode
2494 }));
2495 });
2496 } else {
2497 database.logAudit(userByUsername.id, 'LOGIN_FAILED', 'auth', userByUsername.id.toString(), 'Invalid password', ipAddress);
2498 res.writeHead(401, { 'Content-Type': 'application/json' });
2499 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2500 }
2501 });
2502
2503 return;
2504 }
2505
2506 if (database.verifyPassword(password, user.password)) {
2507 const isFirstTimeLogin = user.force_password_change === 1;
2508
2509 const twoFACode = generateVerificationCode();
2510 verificationCodes.set(user.email, {
2511 code: twoFACode,
2512 timestamp: Date.now(),
2513 userId: user.id,
2514 isFirstTimeLogin: isFirstTimeLogin,
2515 userType: user.user_type,
2516 needsPasswordChange: isFirstTimeLogin
2517 });
2518
2519 send2FACode(user.email, twoFACode)
2520 .then(() => {
2521 res.writeHead(200, { 'Content-Type': 'application/json' });
2522 res.end(JSON.stringify({
2523 success: true,
2524 message: 'Two-factor authentication code sent to your email',
2525 requires2FA: true,
2526 email: user.email,
2527 username: user.username,
2528 isFirstTimeLogin: isFirstTimeLogin,
2529 userType: user.user_type
2530 }));
2531 })
2532 .catch(error => {
2533 console.error('Error sending 2FA email:', error);
2534 res.writeHead(200, { 'Content-Type': 'application/json' });
2535 res.end(JSON.stringify({
2536 success: true,
2537 message: 'Two-factor authentication required',
2538 requires2FA: true,
2539 email: user.email,
2540 username: user.username,
2541 isFirstTimeLogin: isFirstTimeLogin,
2542 userType: user.user_type,
2543 developmentCode: twoFACode
2544 }));
2545 });
2546 } else {
2547 database.logAudit(user.id, 'LOGIN_FAILED', 'auth', user.id.toString(), 'Invalid password', ipAddress);
2548 res.writeHead(401, { 'Content-Type': 'application/json' });
2549 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2550 }
2551 }
2552 );
2553 }
2554 );
2555 }
2556 );
2557 });
2558
2559 return;
2560 }
2561
2562 // No user found in any table
2563 database.logAudit(null, 'LOGIN_FAILED', 'auth', null, `Failed login attempt for email: ${email}`, ipAddress);
2564 res.writeHead(401, { 'Content-Type': 'application/json' });
2565 res.end(JSON.stringify({ success: false, message: 'Invalid email or password' }));
2566 });
2567 });
2568 });
2569 }
2570
2571 else if (pathname === '/api/resend-2fa' && req.method === 'POST') {
2572 let body = '';
2573 req.on('data', chunk => {
2574 body += chunk.toString();
2575 });
2576 req.on('end', () => {
2577 const { email } = JSON.parse(body);
2578
2579 if (!email) {
2580 res.writeHead(400, { 'Content-Type': 'application/json' });
2581 res.end(JSON.stringify({ success: false, message: 'Email is required' }));
2582 return;
2583 }
2584
2585 database.database.get(
2586 'SELECT * FROM users WHERE email = $1',
2587 [email],
2588 (err, user) => {
2589 if (err || !user) {
2590 database.getUserByUsername(email, (err, userByUsername) => {
2591 if (err || !userByUsername) {
2592 res.writeHead(400, { 'Content-Type': 'application/json' });
2593 res.end(JSON.stringify({ success: false, message: 'User not found' }));
2594 return;
2595 }
2596
2597 const newTwoFACode = generateVerificationCode();
2598 verificationCodes.set(userByUsername.email, {
2599 code: newTwoFACode,
2600 timestamp: Date.now(),
2601 userId: userByUsername.id,
2602 isFirstTimeLogin: userByUsername.force_password_change === 1,
2603 needsPasswordChange: userByUsername.force_password_change === 1,
2604 userType: userByUsername.user_type
2605 });
2606
2607 console.log(`🔄 Resent 2FA code for ${userByUsername.email}, expires in 30 seconds`);
2608
2609 send2FACode(userByUsername.email, newTwoFACode)
2610 .then(() => {
2611 res.writeHead(200, { 'Content-Type': 'application/json' });
2612 res.end(JSON.stringify({
2613 success: true,
2614 message: 'New two-factor authentication code sent to your email (expires in 30 seconds)',
2615 email: userByUsername.email
2616 }));
2617 })
2618 .catch(error => {
2619 console.error('Error sending 2FA email:', error.message);
2620 res.writeHead(200, { 'Content-Type': 'application/json' });
2621 res.end(JSON.stringify({
2622 success: true,
2623 message: 'New two-factor authentication code generated (check console, expires in 30 seconds)',
2624 email: userByUsername.email,
2625 developmentCode: newTwoFACode
2626 }));
2627 });
2628 });
2629
2630 return;
2631 }
2632
2633 const newTwoFACode = generateVerificationCode();
2634 verificationCodes.set(user.email, {
2635 code: newTwoFACode,
2636 timestamp: Date.now(),
2637 userId: user.id,
2638 isFirstTimeLogin: user.force_password_change === 1,
2639 needsPasswordChange: user.force_password_change === 1,
2640 userType: user.user_type
2641 });
2642
2643 console.log(`🔄 Resent 2FA code for ${user.email}, expires in 30 seconds`);
2644
2645 send2FACode(user.email, newTwoFACode)
2646 .then(() => {
2647 res.writeHead(200, { 'Content-Type': 'application/json' });
2648 res.end(JSON.stringify({
2649 success: true,
2650 message: 'New two-factor authentication code sent to your email (expires in 30 seconds)',
2651 email: user.email
2652 }));
2653 })
2654 .catch(error => {
2655 console.error('Error sending 2FA email:', error.message);
2656 res.writeHead(200, { 'Content-Type': 'application/json' });
2657 res.end(JSON.stringify({
2658 success: true,
2659 message: 'New two-factor authentication code generated (check console, expires in 30 seconds)',
2660 email: user.email,
2661 developmentCode: newTwoFACode
2662 }));
2663 });
2664 }
2665 );
2666 });
2667 }
2668
2669 else if (pathname === '/api/verify-2fa' && req.method === 'POST') {
2670 let body = '';
2671 req.on('data', chunk => {
2672 body += chunk.toString();
2673 });
2674 req.on('end', () => {
2675 const { email, code } = JSON.parse(body);
2676
2677 if (!email || !code) {
2678 res.writeHead(400, { 'Content-Type': 'application/json' });
2679 res.end(JSON.stringify({ success: false, message: 'All fields are required' }));
2680 return;
2681 }
2682
2683 const verificationData = verificationCodes.get(email);
2684
2685 if (!verificationData || verificationData.code !== code) {
2686 res.writeHead(400, { 'Content-Type': 'application/json' });
2687 res.end(JSON.stringify({ success: false, message: 'Invalid two-factor authentication code' }));
2688 return;
2689 }
2690
2691 if (Date.now() - verificationData.timestamp > 30 * 1000) {
2692 verificationCodes.delete(email);
2693 res.writeHead(400, { 'Content-Type': 'application/json' });
2694 res.end(JSON.stringify({ success: false, message: 'Two-factor authentication code has expired. Please request a new one.' }));
2695 return;
2696 }
2697
2698 // Check if this is a first-time login that requires password change
2699 if (verificationData.needsPasswordChange) {
2700 const tempSessionId = generateSessionId();
2701 tempAdminSessions.set(tempSessionId, verificationData.userId);
2702
2703 database.logAudit(verificationData.userId, 'LOGIN_2FA_SUCCESS_PASSWORD_CHANGE_REQUIRED', 'auth', verificationData.userId.toString(),
2704 `${verificationData.userType} first login, password change required`, ipAddress);
2705
2706 verificationCodes.delete(email);
2707
2708 // Determine redirect based on user type
2709 let redirectTo = 'change-password.html?forced=true';
2710 if (verificationData.userType === 'store_owner') {
2711 redirectTo = 'change-password.html?forced=true&redirect=store-owner.html';
2712 } else if (verificationData.userType === 'store_employee') {
2713 redirectTo = 'change-password.html?forced=true&redirect=store-employee.html';
2714 } else if (verificationData.userType === 'admin') {
2715 redirectTo = 'change-password.html?forced=true&redirect=admin.html';
2716 } else if (verificationData.userType === 'client') {
2717 redirectTo = 'change-password.html?forced=true&redirect=client-dashboard.html';
2718 }
2719
2720 res.writeHead(200, {
2721 'Content-Type': 'application/json',
2722 'Set-Cookie': `sessionId=${tempSessionId}; HttpOnly; Path=/; Max-Age=3600; SameSite=Strict`
2723 });
2724
2725 res.end(JSON.stringify({
2726 success: true,
2727 message: 'Two-factor authentication successful. Password change required.',
2728 requiresPasswordChange: true,
2729 userType: verificationData.userType,
2730 redirectTo: redirectTo
2731 }));
2732
2733 return;
2734 }
2735
2736 // Regular login - create session and redirect based on user type
2737 const sessionId = generateSessionId();
2738
2739 // Determine how to store the user ID in session
2740 if (verificationData.userType === 'client') {
2741 sessions.set(sessionId, `client_${verificationData.userId}`);
2742 } else if (verificationData.userType === 'store_owner' || verificationData.userType === 'store_employee') {
2743 sessions.set(sessionId, `personal_${verificationData.userId}`);
2744 } else {
2745 sessions.set(sessionId, verificationData.userId.toString());
2746 }
2747
2748 verificationCodes.delete(email);
2749
2750 database.logAudit(verificationData.userId, 'LOGIN_SUCCESS', 'auth', verificationData.userId.toString(),
2751 `${verificationData.userType} logged in successfully`, ipAddress);
2752
2753 // Determine redirect based on user type
2754 let redirectTo = '';
2755
2756 switch(verificationData.userType) {
2757 case 'client':
2758 redirectTo = 'client-dashboard.html';
2759 break;
2760 case 'store_owner':
2761 redirectTo = 'store-owner.html';
2762 break;
2763 case 'store_employee':
2764 redirectTo = 'store-employee.html';
2765 break;
2766 case 'admin':
2767 redirectTo = 'admin.html';
2768 break;
2769 default:
2770 redirectTo = 'dashboard.html';
2771 }
2772
2773 console.log(`✅ ${verificationData.userType} login successful. Redirecting to: ${redirectTo}`);
2774
2775 res.writeHead(200, {
2776 'Content-Type': 'application/json',
2777 'Set-Cookie': `sessionId=${sessionId}; HttpOnly; Path=/; Max-Age=3600; SameSite=Strict`
2778 });
2779
2780 res.end(JSON.stringify({
2781 success: true,
2782 message: 'Successfully logged in',
2783 userType: verificationData.userType,
2784 redirectTo: redirectTo
2785 }));
2786 });
2787 }
2788
2789 else if (pathname === '/api/logout' && req.method === 'POST') {
2790 const cookies = parseCookies(req);
2791 const sessionId = cookies.sessionId;
2792
2793 if (sessionId) {
2794 const userId = sessions.get(sessionId);
2795 if (userId) {
2796 database.logAudit(userId, 'LOGOUT', 'auth', userId.toString(), 'User logged out', ipAddress);
2797 }
2798 sessions.delete(sessionId);
2799 tempAdminSessions.delete(sessionId);
2800 }
2801
2802 res.writeHead(200, {
2803 'Content-Type': 'application/json',
2804 'Set-Cookie': 'sessionId=; HttpOnly; Path=/; Expires=Thu, 01 Jan 1970 00:00:00 GMT; SameSite=Strict'
2805 });
2806
2807 res.end(JSON.stringify({ success: true, message: 'Successfully logged out' }));
2808 }
2809
2810 else if (pathname === '/api/user' && req.method === 'GET') {
2811 requireAuth(req, res, (userId) => {
2812 const cookies = parseCookies(req);
2813 const sessionId = cookies.sessionId;
2814
2815 if (tempAdminSessions.has(sessionId)) {
2816 // This is a temporary session (password change required)
2817 // Get user info to determine type
2818 database.getUserById(userId, (err, user) => {
2819 if (err || !user) {
2820 // Check if it's a personal user
2821 database.getPersonalById(userId, (err, personal) => {
2822 if (err || !personal) {
2823 res.writeHead(200, { 'Content-Type': 'application/json' });
2824 res.end(JSON.stringify({
2825 success: true,
2826 user: {
2827 id: userId,
2828 username: 'admin',
2829 userType: 'admin',
2830 needsPasswordChange: true
2831 },
2832 isTempSession: true
2833 }));
2834 } else {
2835 // Personal user (store owner/employee)
2836 database.database.get(
2837 'SELECT boss_id FROM boss WHERE boss_id = $1',
2838 [userId],
2839 (err, boss) => {
2840 let userType = 'store_employee';
2841 if (boss) {
2842 userType = 'store_owner';
2843 }
2844
2845 res.writeHead(200, { 'Content-Type': 'application/json' });
2846 res.end(JSON.stringify({
2847 success: true,
2848 user: {
2849 id: personal.id,
2850 firstName: personal.first_name,
2851 lastName: personal.last_name,
2852 email: personal.email,
2853 userType: userType,
2854 needsPasswordChange: true
2855 },
2856 isTempSession: true
2857 }));
2858 }
2859 );
2860 }
2861 });
2862 } else {
2863 // Regular user (admin)
2864 res.writeHead(200, { 'Content-Type': 'application/json' });
2865 res.end(JSON.stringify({
2866 success: true,
2867 user: {
2868 id: user.id,
2869 username: user.username,
2870 email: user.email,
2871 userType: user.user_type || 'admin',
2872 needsPasswordChange: true
2873 },
2874 isTempSession: true
2875 }));
2876 }
2877 });
2878
2879 return;
2880 }
2881
2882 // Regular session
2883 const userIdStr = String(userId);
2884
2885 if (userIdStr === '000000') {
2886 // Admin user
2887 database.getUserById(userIdStr, (err, user) => {
2888 if (err || !user) {
2889 res.writeHead(404, { 'Content-Type': 'application/json' });
2890 res.end(JSON.stringify({ success: false, message: 'User not found' }));
2891 } else {
2892 res.writeHead(200, { 'Content-Type': 'application/json' });
2893 res.end(JSON.stringify({
2894 success: true,
2895 user: {
2896 id: user.id,
2897 username: user.username,
2898 email: user.email,
2899 userType: 'admin'
2900 }
2901 }));
2902 }
2903 });
2904 }
2905 else if (userIdStr.startsWith('client_')) {
2906 const clientId = parseInt(userIdStr.replace('client_', ''));
2907
2908 database.getClientById(clientId, (err, client) => {
2909 if (err || !client) {
2910 res.writeHead(404, { 'Content-Type': 'application/json' });
2911 res.end(JSON.stringify({ success: false, message: 'User not found' }));
2912 } else {
2913 res.writeHead(200, { 'Content-Type': 'application/json' });
2914 res.end(JSON.stringify({
2915 success: true,
2916 user: {
2917 id: client.client_ID,
2918 firstName: client.first_name,
2919 lastName: client.last_name,
2920 email: client.email,
2921 userType: 'client'
2922 }
2923 }));
2924 }
2925 });
2926 }
2927 else if (userIdStr.startsWith('personal_')) {
2928 const personalId = userIdStr.replace('personal_', '');
2929
2930 database.getPersonalById(personalId, (err, personal) => {
2931 if (err || !personal) {
2932 res.writeHead(404, { 'Content-Type': 'application/json' });
2933 res.end(JSON.stringify({ success: false, message: 'User not found' }));
2934 return;
2935 }
2936
2937 database.database.get(
2938 'SELECT boss_id FROM boss WHERE boss_id = $1',
2939 [personalId],
2940 (err, boss) => {
2941 if (err) {
2942 console.error('Error checking boss:', err);
2943 }
2944
2945 if (boss) {
2946 database.database.all(
2947 `SELECT s.* FROM store s
2948 JOIN works_in_store w ON s.store_id = w.store_id
2949 WHERE w.personal_id = $1`,
2950 [personalId],
2951 (err, stores) => {
2952 if (err) {
2953 console.error('Error getting stores:', err);
2954 stores = [];
2955 }
2956
2957 res.writeHead(200, { 'Content-Type': 'application/json' });
2958 res.end(JSON.stringify({
2959 success: true,
2960 user: {
2961 id: personal.id,
2962 firstName: personal.first_name,
2963 lastName: personal.last_name,
2964 email: personal.email,
2965 userType: 'store_owner',
2966 stores: stores
2967 }
2968 }));
2969 }
2970 );
2971 } else {
2972 database.database.get(
2973 'SELECT employee_id FROM employees WHERE employee_id = $1',
2974 [personalId],
2975 (err, employee) => {
2976 if (err) {
2977 console.error('Error checking employee:', err);
2978 }
2979
2980 if (employee) {
2981 database.database.all(
2982 `SELECT s.* FROM store s
2983 JOIN works_in_store w ON s.store_id = w.store_id
2984 WHERE w.personal_id = $1`,
2985 [personalId],
2986 (err, stores) => {
2987 if (err) {
2988 console.error('Error getting stores:', err);
2989 stores = [];
2990 }
2991
2992 res.writeHead(200, { 'Content-Type': 'application/json' });
2993 res.end(JSON.stringify({
2994 success: true,
2995 user: {
2996 id: personal.id,
2997 firstName: personal.first_name,
2998 lastName: personal.last_name,
2999 email: personal.email,
3000 userType: 'store_employee',
3001 stores: stores
3002 }
3003 }));
3004 }
3005 );
3006 } else {
3007 res.writeHead(404, { 'Content-Type': 'application/json' });
3008 res.end(JSON.stringify({ success: false, message: 'User type not recognized' }));
3009 }
3010 }
3011 );
3012 }
3013 }
3014 );
3015 });
3016 } else {
3017 database.getUserById(userIdStr, (err, user) => {
3018 if (err || !user) {
3019 res.writeHead(404, { 'Content-Type': 'application/json' });
3020 res.end(JSON.stringify({ success: false, message: 'User not found' }));
3021 } else {
3022 res.writeHead(200, { 'Content-Type': 'application/json' });
3023 res.end(JSON.stringify({ success: true, user }));
3024 }
3025 });
3026 }
3027 });
3028 }
3029
3030 else if (pathname === '/api/products' && req.method === 'GET') {
3031 const query = parsedUrl.query;
3032 const categoryId = query.category;
3033 const searchTerm = query.search;
3034
3035 database.getProducts(categoryId, searchTerm, (err, products) => {
3036 if (err) {
3037 res.writeHead(500, { 'Content-Type': 'application/json' });
3038 res.end(JSON.stringify({ success: false, message: 'Error fetching products' }));
3039 } else {
3040 res.writeHead(200, { 'Content-Type': 'application/json' });
3041 res.end(JSON.stringify({ success: true, products }));
3042 }
3043 });
3044 }
3045
3046 else if (pathname === '/api/product' && req.method === 'GET') {
3047 const productId = parsedUrl.query.id;
3048
3049 if (!productId) {
3050 res.writeHead(400, { 'Content-Type': 'application/json' });
3051 res.end(JSON.stringify({ success: false, message: 'Product ID is required' }));
3052 return;
3053 }
3054
3055 database.getProductById(productId, (err, product) => {
3056 if (err) {
3057 res.writeHead(500, { 'Content-Type': 'application/json' });
3058 res.end(JSON.stringify({ success: false, message: 'Error fetching product' }));
3059 } else if (!product) {
3060 res.writeHead(404, { 'Content-Type': 'application/json' });
3061 res.end(JSON.stringify({ success: false, message: 'Product not found' }));
3062 } else {
3063 res.writeHead(200, { 'Content-Type': 'application/json' });
3064 res.end(JSON.stringify({ success: true, product }));
3065 }
3066 });
3067 }
3068
3069 else if (pathname === '/api/create-category' && req.method === 'POST') {
3070 requireStoreOwner()(req, res, (personalId) => {
3071 let body = '';
3072 req.on('data', chunk => {
3073 body += chunk.toString();
3074 });
3075 req.on('end', () => {
3076 const categoryData = JSON.parse(body);
3077
3078 if (!categoryData.name || !categoryData.name.trim()) {
3079 res.writeHead(400, { 'Content-Type': 'application/json' });
3080 res.end(JSON.stringify({ success: false, message: 'Category name is required' }));
3081 return;
3082 }
3083
3084 const dbCategoryData = {
3085 name: categoryData.name.trim(),
3086 description: (categoryData.description || '').trim(),
3087 parent_id: categoryData.parentId ? parseInt(categoryData.parentId) : null
3088 };
3089
3090 database.createCategory(dbCategoryData, (err, category) => {
3091 if (err) {
3092 console.error('Error creating category:', err);
3093 res.writeHead(500, { 'Content-Type': 'application/json' });
3094 res.end(JSON.stringify({ success: false, message: 'Error creating category: ' + err.message }));
3095 } else if (!category) {
3096 res.writeHead(500, { 'Content-Type': 'application/json' });
3097 res.end(JSON.stringify({ success: false, message: 'Failed to create category' }));
3098 } else {
3099 database.logAudit(personalId, 'CATEGORY_CREATED', 'category', category.id.toString(), `New category created: ${category.name}`, ipAddress);
3100
3101 res.writeHead(200, { 'Content-Type': 'application/json' });
3102 res.end(JSON.stringify({
3103 success: true,
3104 message: 'Category created successfully',
3105 category: {
3106 id: category.id,
3107 name: category.name,
3108 parent_id: category.parent_category_id,
3109 description: category.description
3110 }
3111 }));
3112 }
3113 });
3114 });
3115 });
3116 }
3117
3118 else if (pathname === '/api/categories' && req.method === 'GET') {
3119 database.getCategoriesWithParents((err, categories) => {
3120 if (err) {
3121 console.error('Error fetching categories:', err);
3122 database.getCategories((err, categories) => {
3123 if (err) {
3124 console.error('Error fetching categories (fallback):', err);
3125 res.writeHead(500, { 'Content-Type': 'application/json' });
3126 res.end(JSON.stringify({ success: false, message: 'Error fetching categories' }));
3127 } else {
3128 res.writeHead(200, { 'Content-Type': 'application/json' });
3129 res.end(JSON.stringify({ success: true, categories: categories || [] }));
3130 }
3131 });
3132 } else {
3133 res.writeHead(200, { 'Content-Type': 'application/json' });
3134 res.end(JSON.stringify({ success: true, categories: categories || [] }));
3135 }
3136 });
3137 }
3138
3139 else if (pathname === '/api/stores' && req.method === 'GET') {
3140 database.getStores((err, stores) => {
3141 if (err) {
3142 res.writeHead(500, { 'Content-Type': 'application/json' });
3143 res.end(JSON.stringify({ success: false, message: 'Error fetching stores' }));
3144 } else {
3145 res.writeHead(200, { 'Content-Type': 'application/json' });
3146 res.end(JSON.stringify({ success: true, stores }));
3147 }
3148 });
3149 }
3150
3151 else if (pathname === '/api/create-order' && req.method === 'POST') {
3152 requireAuth(req, res, (userId) => {
3153 let body = '';
3154 req.on('data', chunk => {
3155 body += chunk.toString();
3156 });
3157 req.on('end', () => {
3158 const orderData = JSON.parse(body);
3159 const userIdStr = String(userId);
3160
3161 if (userIdStr.startsWith('client_')) {
3162 const clientId = parseInt(userIdStr.replace('client_', ''));
3163 const storeId = orderData.storeId;
3164
3165 if (!storeId) {
3166 res.writeHead(400, { 'Content-Type': 'application/json' });
3167 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
3168 return;
3169 }
3170
3171 const year = new Date().getFullYear().toString().slice(-3);
3172
3173 database.database.get(
3174 'SELECT COUNT(*) AS order_count FROM "order" WHERE LEFT(order_num,3) = $1 AND SUBSTRING(order_num FROM 4 FOR 3)::INTEGER = ($2::INTEGER % 1000)',
3175 [storeId, new Date().getFullYear().toString()],
3176 (err, result) => {
3177 if (err) {
3178 console.error('Error counting orders:', err);
3179 res.writeHead(500, { 'Content-Type': 'application/json' });
3180 res.end(JSON.stringify({ success: false, message: 'Error generating order ID' }));
3181 return;
3182 }
3183
3184 const orderCount = result ? result.order_count + 1 : 1;
3185 const orderNumPadded = orderCount.toString().padStart(5, '0');
3186
3187 // Format order number: storeId + year (3 digits) + orderNum (5 digits)
3188 const orderNum = storeId + year + orderNumPadded;
3189
3190 const newOrderData = {
3191 order_num: orderNum,
3192 client_id: clientId,
3193 store_id: storeId,
3194 quantity: orderData.items.reduce((sum, item) => sum + item.quantity, 0),
3195 payment_method: orderData.paymentMethod || 'credit card',
3196 discount: orderData.discount || 0,
3197 delivery_address: orderData.deliveryAddress || 'Not specified',
3198 items: orderData.items.map(item => ({
3199 product_code: item.productCode,
3200 quantity: item.quantity,
3201 price: item.price
3202 }))
3203 };
3204
3205 database.createOrderNew(newOrderData, (err, orderId) => {
3206 if (err) {
3207 res.writeHead(500, { 'Content-Type': 'application/json' });
3208 res.end(JSON.stringify({ success: false, message: 'Error creating order' }));
3209 } else {
3210 database.logAudit(clientId, 'ORDER_CREATED', 'order', orderId.toString(), 'New order created', ipAddress);
3211 res.writeHead(200, { 'Content-Type': 'application/json' });
3212 res.end(JSON.stringify({ success: true, orderId, message: 'Order created successfully' }));
3213 }
3214 });
3215 }
3216 );
3217 } else {
3218 res.writeHead(403, { 'Content-Type': 'application/json' });
3219 res.end(JSON.stringify({ success: false, message: 'Only clients can create orders' }));
3220 }
3221 });
3222 });
3223 }
3224
3225 else if (pathname === '/api/user-orders' && req.method === 'GET') {
3226 requireAuth(req, res, (userId) => {
3227 const userIdStr = String(userId);
3228
3229 if (userIdStr.startsWith('client_')) {
3230 const clientId = parseInt(userIdStr.replace('client_', ''));
3231
3232 database.getOrdersByClient(clientId, (err, orders) => {
3233 if (err) {
3234 res.writeHead(500, { 'Content-Type': 'application/json' });
3235 res.end(JSON.stringify({ success: false, message: 'Error fetching orders' }));
3236 } else {
3237 res.writeHead(200, { 'Content-Type': 'application/json' });
3238 res.end(JSON.stringify({ success: true, orders }));
3239 }
3240 });
3241 } else {
3242 res.writeHead(403, { 'Content-Type': 'application/json' });
3243 res.end(JSON.stringify({ success: false, message: 'Only clients can view orders' }));
3244 }
3245 });
3246 }
3247
3248 else if (pathname === '/api/create-review' && req.method === 'POST') {
3249 requireAuth(req, res, (userId) => {
3250 let body = '';
3251 req.on('data', chunk => {
3252 body += chunk.toString();
3253 });
3254 req.on('end', () => {
3255 const reviewData = JSON.parse(body);
3256 const userIdStr = String(userId);
3257
3258 if (userIdStr.startsWith('client_')) {
3259 const clientId = parseInt(userIdStr.replace('client_', ''));
3260 reviewData.client_id = clientId;
3261
3262 database.createReviewNew(reviewData, (err, reviewId) => {
3263 if (err) {
3264 res.writeHead(500, { 'Content-Type': 'application/json' });
3265 res.end(JSON.stringify({ success: false, message: 'Error creating review' }));
3266 } else {
3267 database.logAudit(clientId, 'REVIEW_CREATED', 'review', reviewId.toString(), 'New review created', ipAddress);
3268 res.writeHead(200, { 'Content-Type': 'application/json' });
3269 res.end(JSON.stringify({ success: true, reviewId, message: 'Review created successfully' }));
3270 }
3271 });
3272 } else {
3273 res.writeHead(403, { 'Content-Type': 'application/json' });
3274 res.end(JSON.stringify({ success: false, message: 'Only clients can create reviews' }));
3275 }
3276 });
3277 });
3278 }
3279
3280 else if (pathname === '/api/create-request' && req.method === 'POST') {
3281 requireAuth(req, res, (userId) => {
3282 let body = '';
3283 req.on('data', chunk => {
3284 body += chunk.toString();
3285 });
3286 req.on('end', () => {
3287 const requestData = JSON.parse(body);
3288 const userIdStr = String(userId);
3289
3290 if (userIdStr.startsWith('client_')) {
3291 const clientId = parseInt(userIdStr.replace('client_', ''));
3292 const storeId = requestData.storeId;
3293
3294 if (!storeId) {
3295 res.writeHead(400, { 'Content-Type': 'application/json' });
3296 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
3297 return;
3298 }
3299
3300 const now = new Date();
3301 const month = (now.getMonth() + 1).toString().padStart(2, '0');
3302 const year = now.getFullYear().toString().slice(-3);
3303
3304 database.database.get(
3305 'SELECT COUNT(*)::int AS request_count FROM request r JOIN for_store fs ON fs.request_num=r.request_num WHERE fs.store_ID = $1 AND EXTRACT(YEAR FROM r.date_and_time)::int = $2::int AND EXTRACT(MONTH FROM r.date_and_time)::int = $3::int',
3306 [storeId, now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')],
3307 (err, result) => {
3308 if (err) {
3309 console.error('Error counting requests:', err);
3310 res.writeHead(500, { 'Content-Type': 'application/json' });
3311 res.end(JSON.stringify({ success: false, message: 'Error generating request ID' }));
3312 return;
3313 }
3314
3315 const requestCount = result ? result.request_count + 1 : 1;
3316 const requestSeqPadded = requestCount.toString().padStart(2, '0');
3317
3318 // Format request number: storeId + month (2 digits) + year (3 digits) + clientId + seq (2 digits)
3319 const requestNum = storeId + month + year + clientId + requestSeqPadded;
3320
3321 const newRequestData = {
3322 request_num: requestNum,
3323 date_and_time: now.toISOString(),
3324 problem: requestData.problem,
3325 client_id: clientId,
3326 store_id: storeId
3327 };
3328
3329 database.createRequest(newRequestData, (err, requestId) => {
3330 if (err) {
3331 res.writeHead(500, { 'Content-Type': 'application/json' });
3332 res.end(JSON.stringify({ success: false, message: 'Error creating request' }));
3333 } else {
3334 database.logAudit(clientId, 'REQUEST_CREATED', 'request', requestId.toString(), 'New request created', ipAddress);
3335 res.writeHead(200, { 'Content-Type': 'application/json' });
3336 res.end(JSON.stringify({ success: true, requestId, message: 'Request created successfully' }));
3337 }
3338 });
3339 }
3340 );
3341 } else {
3342 res.writeHead(403, { 'Content-Type': 'application/json' });
3343 res.end(JSON.stringify({ success: false, message: 'Only clients can create requests' }));
3344 }
3345 });
3346 });
3347 }
3348
3349 else if (pathname === '/api/create-refund' && req.method === 'POST') {
3350 requireAuth(req, res, (userId) => {
3351 let body = '';
3352 req.on('data', chunk => {
3353 body += chunk.toString();
3354 });
3355 req.on('end', () => {
3356 const refundData = JSON.parse(body);
3357 const userIdStr = String(userId);
3358
3359 if (userIdStr.startsWith('client_')) {
3360 const clientId = parseInt(userIdStr.replace('client_', ''));
3361
3362 database.database.get(
3363 'SELECT LEFT(order_num,3) AS store_id FROM "order" WHERE order_num = $1',
3364 [refundData.order_num],
3365 (err, result) => {
3366 if (err || !result) {
3367 res.writeHead(404, { 'Content-Type': 'application/json' });
3368 res.end(JSON.stringify({ success: false, message: 'Order not found' }));
3369 return;
3370 }
3371
3372 const storeId = result.store_id;
3373 const now = new Date();
3374 const month = (now.getMonth() + 1).toString().padStart(2, '0');
3375 const year = now.getFullYear().toString().slice(-3);
3376
3377 database.database.get(
3378 'SELECT COUNT(*)::int AS refund_count FROM refund WHERE SUBSTRING(refund_id::text FROM 4 FOR 2)::int = $2::int AND SUBSTRING(refund_id::text FROM 6 FOR 3)::int = ($1::int % 1000)',
3379 [now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')],
3380 (err, result) => {
3381 if (err) {
3382 console.error('Error counting refunds:', err);
3383 res.writeHead(500, { 'Content-Type': 'application/json' });
3384 res.end(JSON.stringify({ success: false, message: 'Error generating refund ID' }));
3385 return;
3386 }
3387
3388 const refundCount = result ? result.refund_count + 1 : 1;
3389 const refundSeqPadded = refundCount.toString().padStart(2, '0');
3390
3391 // Format refund ID: storeId + month (2 digits) + year (3 digits) + seq (2 digits)
3392 const refundId = storeId + month + year + refundSeqPadded;
3393
3394 refundData.refund_id = refundId;
3395
3396 database.createRefund(refundData, (err, refundId) => {
3397 if (err) {
3398 res.writeHead(500, { 'Content-Type': 'application/json' });
3399 res.end(JSON.stringify({ success: false, message: 'Error creating refund' }));
3400 } else {
3401 database.logAudit(clientId, 'REFUND_CREATED', 'refund', refundId.toString(), 'New refund requested', ipAddress);
3402 res.writeHead(200, { 'Content-Type': 'application/json' });
3403 res.end(JSON.stringify({ success: true, refundId, message: 'Refund requested successfully' }));
3404 }
3405 });
3406 }
3407 );
3408 }
3409 );
3410 } else {
3411 res.writeHead(403, { 'Content-Type': 'application/json' });
3412 res.end(JSON.stringify({ success: false, message: 'Only clients can request refunds' }));
3413 }
3414 });
3415 });
3416 }
3417
3418 else if (pathname === '/api/add-product' && req.method === 'POST') {
3419 requireStoreOwner()(req, res, (personalId) => {
3420 let body = '';
3421 req.on('data', chunk => {
3422 body += chunk.toString();
3423 });
3424 req.on('end', () => {
3425 const productData = JSON.parse(body);
3426
3427 database.database.get(
3428 'SELECT store_id FROM works_in_store WHERE personal_id = $1',
3429 [personalId],
3430 (err, bossStore) => {
3431 if (err || !bossStore) {
3432 res.writeHead(403, { 'Content-Type': 'application/json' });
3433 res.end(JSON.stringify({ success: false, message: 'Store not found for this owner' }));
3434 return;
3435 }
3436
3437 const storeId = productData.storeId || bossStore.store_id;
3438
3439 if (!storeId) {
3440 res.writeHead(400, { 'Content-Type': 'application/json' });
3441 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
3442 return;
3443 }
3444
3445 database.database.get(
3446 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
3447 [personalId, storeId],
3448 (err, ownsStore) => {
3449 if (err || !ownsStore) {
3450 res.writeHead(403, { 'Content-Type': 'application/json' });
3451 res.end(JSON.stringify({ success: false, message: 'You are not authorized to add products to this store' }));
3452 return;
3453 }
3454
3455 // PostgreSQL: extract the numeric product sequence from the store-prefixed product code
3456 database.database.get(
3457 'SELECT MAX(CAST(SUBSTRING(code FROM 4) AS INTEGER)) AS max_product_num FROM product WHERE LEFT(code,3) = $1',
3458 [storeId],
3459 (err, result) => {
3460 if (err) {
3461 console.error('Error getting max product number:', err);
3462 res.writeHead(500, { 'Content-Type': 'application/json' });
3463 res.end(JSON.stringify({ success: false, message: 'Error generating product code' }));
3464 return;
3465 }
3466
3467 const maxProductNum = result?.max_product_num || 0;
3468 let nextProductNum = maxProductNum + 1;
3469
3470 // Ensure product number doesn't end with 0000
3471 while (nextProductNum % 10000 === 0) {
3472 nextProductNum++;
3473 }
3474
3475 // Format product code: storeId + productNum (4 digits, padded)
3476 const productNumPadded = nextProductNum.toString().padStart(4, '0');
3477 productData.code = storeId + productNumPadded;
3478 productData.store_id = storeId;
3479
3480 database.addProduct(personalId, productData, (err, productId) => {
3481 if (err) {
3482 console.error('Error adding product:', err);
3483 res.writeHead(500, { 'Content-Type': 'application/json' });
3484 res.end(JSON.stringify({
3485 success: false,
3486 message: 'Error adding product: ' + (err.message || 'Unknown error'),
3487 details: err.toString()
3488 }));
3489 } else {
3490 database.logAudit(personalId, 'PRODUCT_ADDED', 'product', productId.toString(), 'New product added', ipAddress);
3491 res.writeHead(200, { 'Content-Type': 'application/json' });
3492 res.end(JSON.stringify({
3493 success: true,
3494 productId,
3495 message: 'Product added successfully',
3496 productCode: productData.code
3497 }));
3498 }
3499 });
3500 }
3501 );
3502 }
3503 );
3504 }
3505 );
3506 });
3507 });
3508 }
3509
3510 else if (pathname === '/api/update-product' && req.method === 'POST') {
3511 requireStoreOwner()(req, res, (personalId) => {
3512 let body = '';
3513 req.on('data', chunk => {
3514 body += chunk.toString();
3515 });
3516 req.on('end', () => {
3517 const productData = JSON.parse(body);
3518
3519 if (!productData.code) {
3520 res.writeHead(400, { 'Content-Type': 'application/json' });
3521 res.end(JSON.stringify({ success: false, message: 'Product code is required' }));
3522 return;
3523 }
3524
3525 database.database.get(
3526 'SELECT LEFT(code,3) AS store_id FROM product WHERE code = $1',
3527 [productData.code],
3528 (err, product) => {
3529 if (err || !product) {
3530 res.writeHead(404, { 'Content-Type': 'application/json' });
3531 res.end(JSON.stringify({ success: false, message: 'Product not found' }));
3532 return;
3533 }
3534
3535 database.database.get(
3536 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
3537 [personalId, product.store_id],
3538 (err, ownsStore) => {
3539 if (err || !ownsStore) {
3540 res.writeHead(403, { 'Content-Type': 'application/json' });
3541 res.end(JSON.stringify({ success: false, message: 'You are not authorized to update products in this store' }));
3542 return;
3543 }
3544
3545 database.updateProduct(personalId, productData, (err, changes) => {
3546 if (err) {
3547 console.error('Error updating product:', err);
3548 res.writeHead(500, { 'Content-Type': 'application/json' });
3549 res.end(JSON.stringify({ success: false, message: 'Error updating product: ' + err.message }));
3550 } else if (changes === 0) {
3551 res.writeHead(404, { 'Content-Type': 'application/json' });
3552 res.end(JSON.stringify({ success: false, message: 'Product not found or no changes made' }));
3553 } else {
3554 database.logAudit(personalId, 'PRODUCT_UPDATED', 'product', productData.code, 'Product updated', ipAddress);
3555 res.writeHead(200, { 'Content-Type': 'application/json' });
3556 res.end(JSON.stringify({ success: true, message: 'Product updated successfully' }));
3557 }
3558 });
3559 }
3560 );
3561 }
3562 );
3563 });
3564 });
3565 }
3566
3567 else if (pathname === '/api/store-reports' && req.method === 'GET') {
3568 requireRole('store_owner')(req, res, (userId, user) => {
3569 database.getStoreReports(userId, (err, reports) => {
3570 if (err) {
3571 res.writeHead(500, { 'Content-Type': 'application/json' });
3572 res.end(JSON.stringify({ success: false, message: 'Error fetching reports' }));
3573 } else {
3574 res.writeHead(200, { 'Content-Type': 'application/json' });
3575 res.end(JSON.stringify({ success: true, reports }));
3576 }
3577 });
3578 });
3579 }
3580
3581 else if (pathname === '/api/all-users' && req.method === 'GET') {
3582 requireRole('admin')(req, res, (userId, user) => {
3583 database.getAllUsers((err, users) => {
3584 if (err) {
3585 res.writeHead(500, { 'Content-Type': 'application/json' });
3586 res.end(JSON.stringify({ success: false, message: 'Error fetching users' }));
3587 } else {
3588 res.writeHead(200, { 'Content-Type': 'application/json' });
3589 res.end(JSON.stringify({ success: true, users }));
3590 }
3591 });
3592 });
3593 }
3594
3595 else if (pathname === '/api/all-orders' && req.method === 'GET') {
3596 requireRole('admin')(req, res, (userId, user) => {
3597 database.getAllOrders((err, orders) => {
3598 if (err) {
3599 res.writeHead(500, { 'Content-Type': 'application/json' });
3600 res.end(JSON.stringify({ success: false, message: 'Error fetching orders' }));
3601 } else {
3602 res.writeHead(200, { 'Content-Type': 'application/json' });
3603 res.end(JSON.stringify({ success: true, orders }));
3604 }
3605 });
3606 });
3607 }
3608
3609 // Updated /api/force-change-password endpoint with redirect handling
3610 else if (pathname === '/api/force-change-password' && req.method === 'POST') {
3611 const cookies = parseCookies(req);
3612 const sessionId = cookies.sessionId;
3613 const userId = tempAdminSessions.get(sessionId);
3614
3615 if (!userId) {
3616 res.writeHead(401, { 'Content-Type': 'application/json' });
3617 res.end(JSON.stringify({ success: false, message: 'Not authenticated or invalid session' }));
3618 return;
3619 }
3620
3621 let body = '';
3622 req.on('data', chunk => {
3623 body += chunk.toString();
3624 });
3625 req.on('end', () => {
3626 try {
3627 const { newPassword, confirmPassword, redirectTo } = JSON.parse(body);
3628
3629 if (!newPassword || !confirmPassword) {
3630 res.writeHead(400, { 'Content-Type': 'application/json' });
3631 res.end(JSON.stringify({ success: false, message: 'All fields are required' }));
3632 return;
3633 }
3634
3635 if (newPassword !== confirmPassword) {
3636 res.writeHead(400, { 'Content-Type': 'application/json' });
3637 res.end(JSON.stringify({ success: false, message: 'New passwords do not match' }));
3638 return;
3639 }
3640
3641 if (!validatePassword(newPassword)) {
3642 res.writeHead(400, { 'Content-Type': 'application/json' });
3643 res.end(JSON.stringify({
3644 success: false,
3645 message: 'Password must have at least 8 characters, including uppercase, lowercase, number and special character'
3646 }));
3647 return;
3648 }
3649
3650 // First, try to find the user in the users table (for admin)
3651 database.getUserById(userId, (err, user) => {
3652 if (err) {
3653 console.error('Error finding user by ID:', err);
3654 }
3655
3656 if (user) {
3657 // Found in users table (admin or regular user)
3658 console.log('Found user in users table:', user);
3659
3660 const hashedPassword = bcrypt.hashSync(newPassword, 10);
3661
3662 database.database.run(
3663 'UPDATE users SET password = $1, force_password_change = FALSE WHERE id = $2',
3664 [hashedPassword, userId],
3665 function(err) {
3666 if (err) {
3667 console.error('Error updating password:', err);
3668 res.writeHead(500, { 'Content-Type': 'application/json' });
3669 res.end(JSON.stringify({ success: false, message: 'Failed to update password' }));
3670 return;
3671 }
3672
3673 // Also update password in personal table if it exists (for admin)
3674 database.database.run(
3675 'UPDATE personal SET password = $1 WHERE id = $2',
3676 [hashedPassword, userId],
3677 function(err) {
3678 if (err) {
3679 console.log('No personal record to update for ID:', userId);
3680 }
3681 }
3682 );
3683
3684 // Clear temp session
3685 tempAdminSessions.delete(sessionId);
3686
3687 // Create new permanent session
3688 const newSessionId = generateSessionId();
3689
3690 // Determine how to store the user ID based on user type
3691 let sessionUserId = String(userId);
3692
3693 if (user.user_type === 'store_owner' || user.user_type === 'store_employee') {
3694 sessionUserId = `personal_${userId}`;
3695 }
3696
3697 sessions.set(newSessionId, sessionUserId);
3698
3699 // Determine redirect based on user type or provided redirectTo
3700 let finalRedirect = redirectTo || 'dashboard.html';
3701
3702 if (!redirectTo) {
3703 if (user.username === 'admin' || user.user_type === 'admin') {
3704 finalRedirect = 'admin.html';
3705 } else if (user.user_type === 'store_owner') {
3706 finalRedirect = 'store-owner.html';
3707 } else if (user.user_type === 'store_employee') {
3708 finalRedirect = 'store-employee.html';
3709 } else if (user.user_type === 'client') {
3710 finalRedirect = 'client-dashboard.html';
3711 }
3712 }
3713
3714 console.log(`Password changed successfully for user ${userId}, redirecting to ${finalRedirect}`);
3715
3716 database.logAudit(userId, 'FORCED_PASSWORD_CHANGE', 'auth', userId.toString(),
3717 `${user.user_type || 'user'} forced password change completed`, ipAddress);
3718
3719 // Set the cookie with proper options
3720 res.writeHead(200, {
3721 'Content-Type': 'application/json',
3722 'Set-Cookie': `sessionId=${newSessionId}; HttpOnly; Path=/; Max-Age=86400; SameSite=Strict` // Extended to 24 hours
3723 });
3724
3725 res.end(JSON.stringify({
3726 success: true,
3727 message: 'Password changed successfully.',
3728 redirectTo: finalRedirect,
3729 userType: user.user_type || 'user'
3730 }));
3731 }
3732 );
3733 } else {
3734 // Not found in users table, check personal table (for store owners/employees)
3735 console.log('User not found in users table, checking personal table for ID:', userId);
3736
3737 database.getPersonalById(userId, (err, personal) => {
3738 if (err) {
3739 console.error('Error finding personal by ID:', err);
3740 }
3741
3742 if (personal) {
3743 console.log('Found user in personal table:', personal);
3744
3745 // Update password in personal table
3746 const hashedPassword = bcrypt.hashSync(newPassword, 10);
3747
3748 database.database.run(
3749 'UPDATE personal SET password = $1 WHERE id = $2',
3750 [hashedPassword, userId],
3751 function(err) {
3752 if (err) {
3753 console.error('Error updating personal password:', err);
3754 res.writeHead(500, { 'Content-Type': 'application/json' });
3755 res.end(JSON.stringify({ success: false, message: 'Failed to update password' }));
3756 return;
3757 }
3758
3759 // Also update in users table if exists
3760 database.database.run(
3761 'UPDATE users SET password = $1, force_password_change = FALSE WHERE email = $2',
3762 [hashedPassword, personal.email],
3763 function(err) {
3764 if (err) {
3765 console.log('No users record to update for email:', personal.email);
3766 }
3767 }
3768 );
3769
3770 // Determine user type (boss/owner or employee)
3771 database.database.get(
3772 'SELECT boss_id FROM boss WHERE boss_id = $1',
3773 [userId],
3774 (err, boss) => {
3775 let userType = 'store_employee';
3776 let finalRedirect = redirectTo || 'store-employee.html';
3777
3778 if (boss) {
3779 userType = 'store_owner';
3780 finalRedirect = redirectTo || 'store-owner.html';
3781 }
3782
3783 // Clear temp session
3784 tempAdminSessions.delete(sessionId);
3785
3786 // Create new permanent session with personal_ prefix
3787 const newSessionId = generateSessionId();
3788 sessions.set(newSessionId, `personal_${userId}`);
3789
3790 console.log(`Password changed successfully for ${userType} ${userId}, redirecting to ${finalRedirect}`);
3791
3792 database.logAudit(userId, 'FORCED_PASSWORD_CHANGE', 'auth', userId.toString(),
3793 `${userType} forced password change completed`, ipAddress);
3794
3795 // Set the cookie with proper options - extended to 24 hours
3796 res.writeHead(200, {
3797 'Content-Type': 'application/json',
3798 'Set-Cookie': `sessionId=${newSessionId}; HttpOnly; Path=/; Max-Age=86400; SameSite=Strict`
3799 });
3800
3801 res.end(JSON.stringify({
3802 success: true,
3803 message: 'Password changed successfully.',
3804 redirectTo: finalRedirect,
3805 userType: userType
3806 }));
3807 }
3808 );
3809 }
3810 );
3811 } else {
3812 // User not found in any table
3813 console.error('User not found in any table with ID:', userId);
3814 res.writeHead(404, { 'Content-Type': 'application/json' });
3815 res.end(JSON.stringify({ success: false, message: 'User not found' }));
3816 }
3817 });
3818 }
3819 });
3820 } catch (parseError) {
3821 console.error('JSON parse error:', parseError);
3822 res.writeHead(400, { 'Content-Type': 'application/json' });
3823 res.end(JSON.stringify({ success: false, message: 'Invalid request format' }));
3824 }
3825 });
3826 }
3827
3828 else if (pathname === '/api/register-employee' && req.method === 'POST') {
3829 requireAuth(req, res, (userId) => {
3830 const userIdStr = String(userId);
3831
3832 // Check if this is the admin user
3833 if (userIdStr === '000000') {
3834 res.writeHead(403, { 'Content-Type': 'application/json' });
3835 res.end(JSON.stringify({ success: false, message: 'Only store owners can register employees' }));
3836 return;
3837 }
3838
3839 if (!userIdStr.startsWith('personal_')) {
3840 res.writeHead(403, { 'Content-Type': 'application/json' });
3841 res.end(JSON.stringify({ success: false, message: 'Only store owners can register employees' }));
3842 return;
3843 }
3844
3845 const personalId = userIdStr.replace('personal_', '');
3846
3847 database.database.get(
3848 'SELECT boss_id FROM boss WHERE boss_id = $1',
3849 [personalId],
3850 (err, boss) => {
3851 if (err || !boss) {
3852 res.writeHead(403, { 'Content-Type': 'application/json' });
3853 res.end(JSON.stringify({ success: false, message: 'Only store owners can register employees' }));
3854 return;
3855 }
3856
3857 let body = '';
3858 req.on('data', chunk => {
3859 body += chunk.toString();
3860 });
3861 req.on('end', () => {
3862 const { firstName, lastName, ssn, email, password, storeId, dateOfHire } = JSON.parse(body);
3863
3864 if (!firstName || !lastName || !ssn || !email || !password || !storeId || !dateOfHire) {
3865 res.writeHead(400, { 'Content-Type': 'application/json' });
3866 res.end(JSON.stringify({ success: false, message: 'All fields are required' }));
3867 return;
3868 }
3869
3870 if (!/^\d{13}$/.test(ssn)) {
3871 res.writeHead(400, { 'Content-Type': 'application/json' });
3872 res.end(JSON.stringify({ success: false, message: 'SSN must be exactly 13 digits' }));
3873 return;
3874 }
3875
3876 if (!validateEmail(email)) {
3877 res.writeHead(400, { 'Content-Type': 'application/json' });
3878 res.end(JSON.stringify({ success: false, message: 'Invalid email format' }));
3879 return;
3880 }
3881
3882 if (!validatePassword(password)) {
3883 res.writeHead(400, { 'Content-Type': 'application/json' });
3884 res.end(JSON.stringify({
3885 success: false,
3886 message: 'Password must have at least 8 characters, including uppercase, lowercase, number and special character'
3887 }));
3888 return;
3889 }
3890
3891 database.getPersonalByEmail(email, (err, existingPersonal) => {
3892 if (err) {
3893 console.error('Error checking personal:', err);
3894 res.writeHead(500, { 'Content-Type': 'application/json' });
3895 res.end(JSON.stringify({ success: false, message: 'Server error checking personal' }));
3896 return;
3897 }
3898
3899 if (existingPersonal) {
3900 res.writeHead(400, { 'Content-Type': 'application/json' });
3901 res.end(JSON.stringify({ success: false, message: 'Email is already registered' }));
3902 return;
3903 }
3904
3905 // Find the next available employee number for this store
3906 database.database.all(
3907 "SELECT id FROM personal WHERE id LIKE '" + storeId + "%' ORDER BY id",
3908 [],
3909 (err, existingEmployees) => {
3910 if (err) {
3911 console.error('Error getting employees:', err);
3912 res.writeHead(500, { 'Content-Type': 'application/json' });
3913 res.end(JSON.stringify({ success: false, message: 'Server error generating employee ID' }));
3914 return;
3915 }
3916
3917 // Find the first available employee number from 001 to 999
3918 let nextEmployeeNum = 1;
3919 const existingNumbers = (existingEmployees || [])
3920 .map(e => {
3921 const num = e.id.substring(3);
3922 return parseInt(num, 10);
3923 })
3924 .filter(num => !isNaN(num));
3925
3926 existingNumbers.sort((a, b) => a - b);
3927
3928 // Find the first gap in the sequence
3929 for (let i = 1; i <= 999; i++) {
3930 if (!existingNumbers.includes(i)) {
3931 nextEmployeeNum = i;
3932 break;
3933 }
3934 }
3935
3936 if (nextEmployeeNum > 999) {
3937 res.writeHead(400, { 'Content-Type': 'application/json' });
3938 res.end(JSON.stringify({ success: false, message: 'Maximum employees reached for this store' }));
3939 return;
3940 }
3941
3942 const employeeNumPadded = nextEmployeeNum.toString().padStart(3, '0');
3943 const newPersonalId = storeId + employeeNumPadded;
3944
3945 database.database.run('BEGIN TRANSACTION', (err) => {
3946 if (err) {
3947 console.error('Error beginning transaction:', err);
3948 res.writeHead(500, { 'Content-Type': 'application/json' });
3949 res.end(JSON.stringify({ success: false, message: 'Server error during registration' }));
3950 return;
3951 }
3952
3953 database.database.run(
3954 'INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES ($1, $2, $3, $4, $5, $6)',
3955 [
3956 newPersonalId,
3957 firstName,
3958 lastName,
3959 ssn,
3960 email,
3961 bcrypt.hashSync(password, 10)
3962 ],
3963 function(err) {
3964 if (err) {
3965 database.database.run('ROLLBACK');
3966 console.error('Error inserting personal:', err);
3967
3968 if (err.code === '23505') {
3969 res.writeHead(400, { 'Content-Type': 'application/json' });
3970 res.end(JSON.stringify({
3971 success: false,
3972 message: 'This personal ID is already taken. Please try again.'
3973 }));
3974 } else {
3975 res.writeHead(400, { 'Content-Type': 'application/json' });
3976 res.end(JSON.stringify({ success: false, message: 'Error registering employee' }));
3977 }
3978 return;
3979 }
3980
3981 database.database.run(
3982 'INSERT INTO employees (employee_id, date_of_hire) VALUES ($1, $2)',
3983 [newPersonalId, dateOfHire],
3984 (err) => {
3985 if (err) {
3986 database.database.run('ROLLBACK');
3987 console.error('Error inserting employee:', err);
3988 res.writeHead(400, { 'Content-Type': 'application/json' });
3989 res.end(JSON.stringify({ success: false, message: 'Error registering as employee' }));
3990 return;
3991 }
3992
3993 database.database.run(
3994 'INSERT INTO works_in_store (personal_id, store_id) VALUES ($1, $2)',
3995 [newPersonalId, storeId],
3996 (err) => {
3997 if (err) {
3998 database.database.run('ROLLBACK');
3999 console.error('Error inserting works_in_store:', err);
4000 res.writeHead(400, { 'Content-Type': 'application/json' });
4001 res.end(JSON.stringify({ success: false, message: 'Error assigning employee to store' }));
4002 return;
4003 }
4004
4005 database.database.run(
4006 'INSERT INTO permissions (personal_id, type, authorisation) VALUES ($1, $2, $3)',
4007 [newPersonalId, 'EMPLOYEE', 'limited_access'],
4008 (err) => {
4009 if (err) {
4010 console.error('Error inserting permissions:', err);
4011 }
4012
4013 // Also create entry in users table for login with force_password_change = 1
4014 database.database.run(
4015 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ($1, $2, $3, $4, $5, $6)',
4016 [
4017 newPersonalId,
4018 `${firstName} ${lastName}`,
4019 email,
4020 bcrypt.hashSync(password, 10),
4021 'store_employee',
4022 1
4023 ],
4024 (err) => {
4025 if (err) {
4026 console.error('Error creating user entry for employee:', err);
4027 }
4028
4029 database.database.run('COMMIT', (err) => {
4030 if (err) {
4031 database.database.run('ROLLBACK');
4032 console.error('Error committing transaction:', err);
4033 res.writeHead(500, { 'Content-Type': 'application/json' });
4034 res.end(JSON.stringify({ success: false, message: 'Error completing registration' }));
4035 return;
4036 }
4037
4038 database.logAudit(personalId, 'EMPLOYEE_REGISTERED', 'employee', newPersonalId, `Employee registered: ${firstName} ${lastName}`, ipAddress);
4039
4040 res.writeHead(200, { 'Content-Type': 'application/json' });
4041 res.end(JSON.stringify({
4042 success: true,
4043 message: 'Employee registered successfully!',
4044 employeeId: newPersonalId,
4045 name: `${firstName} ${lastName}`
4046 }));
4047 });
4048 }
4049 );
4050 }
4051 );
4052 }
4053 );
4054 }
4055 );
4056 }
4057 );
4058 });
4059 }
4060 );
4061 });
4062 });
4063 }
4064 );
4065 });
4066 }
4067
4068 else if (pathname === '/api/delete-employee' && req.method === 'POST') {
4069 requireAuth(req, res, (userId) => {
4070 const userIdStr = String(userId);
4071
4072 // Check if this is the admin user
4073 if (userIdStr === '000000') {
4074 res.writeHead(403, { 'Content-Type': 'application/json' });
4075 res.end(JSON.stringify({ success: false, message: 'Only store owners can delete employees' }));
4076 return;
4077 }
4078
4079 if (!userIdStr.startsWith('personal_')) {
4080 res.writeHead(403, { 'Content-Type': 'application/json' });
4081 res.end(JSON.stringify({ success: false, message: 'Only store owners can delete employees' }));
4082 return;
4083 }
4084
4085 const personalId = userIdStr.replace('personal_', '');
4086
4087 database.database.get(
4088 'SELECT boss_id FROM boss WHERE boss_id = $1',
4089 [personalId],
4090 (err, boss) => {
4091 if (err || !boss) {
4092 res.writeHead(403, { 'Content-Type': 'application/json' });
4093 res.end(JSON.stringify({ success: false, message: 'Only store owners can delete employees' }));
4094 return;
4095 }
4096
4097 let body = '';
4098 req.on('data', chunk => {
4099 body += chunk.toString();
4100 });
4101 req.on('end', () => {
4102 const { employeeId, storeId } = JSON.parse(body);
4103
4104 if (!employeeId || !storeId) {
4105 res.writeHead(400, { 'Content-Type': 'application/json' });
4106 res.end(JSON.stringify({ success: false, message: 'Employee ID and Store ID are required' }));
4107 return;
4108 }
4109
4110 database.database.get(
4111 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4112 [personalId, storeId],
4113 (err, bossStore) => {
4114 if (err || !bossStore) {
4115 res.writeHead(403, { 'Content-Type': 'application/json' });
4116 res.end(JSON.stringify({ success: false, message: 'You are not authorized to manage employees in this store' }));
4117 return;
4118 }
4119
4120 database.database.get(
4121 'SELECT personal_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4122 [employeeId, storeId],
4123 (err, employeeStore) => {
4124 if (err || !employeeStore) {
4125 res.writeHead(404, { 'Content-Type': 'application/json' });
4126 res.end(JSON.stringify({ success: false, message: 'Employee not found in this store' }));
4127 return;
4128 }
4129
4130 database.database.get(
4131 'SELECT boss_id FROM boss WHERE boss_id = $1',
4132 [employeeId],
4133 (err, isBoss) => {
4134 if (err) {
4135 console.error('Error checking if employee is boss:', err);
4136 }
4137
4138 if (isBoss) {
4139 res.writeHead(403, { 'Content-Type': 'application/json' });
4140 res.end(JSON.stringify({ success: false, message: 'Cannot delete store owners' }));
4141 return;
4142 }
4143
4144 database.database.run('BEGIN TRANSACTION', (err) => {
4145 if (err) {
4146 console.error('Error beginning transaction:', err);
4147 res.writeHead(500, { 'Content-Type': 'application/json' });
4148 res.end(JSON.stringify({ success: false, message: 'Server error during deletion' }));
4149 return;
4150 }
4151
4152 database.database.run(
4153 'DELETE FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4154 [employeeId, storeId],
4155 (err) => {
4156 if (err) {
4157 database.database.run('ROLLBACK');
4158 console.error('Error deleting from works_in_store:', err);
4159 res.writeHead(500, { 'Content-Type': 'application/json' });
4160 res.end(JSON.stringify({ success: false, message: 'Error removing employee from store' }));
4161 return;
4162 }
4163
4164 database.database.run(
4165 'DELETE FROM employees WHERE employee_id = $1',
4166 [employeeId],
4167 (err) => {
4168 if (err) {
4169 console.error('Error deleting from employees:', err);
4170 }
4171
4172 database.database.run(
4173 'DELETE FROM permissions WHERE personal_id = $1',
4174 [employeeId],
4175 (err) => {
4176 if (err) {
4177 console.error('Error deleting from permissions:', err);
4178 }
4179
4180 database.database.run(
4181 'DELETE FROM personal WHERE id = $1',
4182 [employeeId],
4183 (err) => {
4184 if (err) {
4185 console.error('Error deleting from personal:', err);
4186 }
4187
4188 // Also delete from users table
4189 database.database.run(
4190 'DELETE FROM users WHERE id = $1',
4191 [employeeId],
4192 (err) => {
4193 if (err) {
4194 console.error('Error deleting from users:', err);
4195 }
4196
4197 database.database.run('COMMIT', (commitErr) => {
4198 if (commitErr) {
4199 database.database.run('ROLLBACK');
4200 console.error('Error committing transaction:', commitErr);
4201 res.writeHead(500, { 'Content-Type': 'application/json' });
4202 res.end(JSON.stringify({ success: false, message: 'Error completing deletion' }));
4203 return;
4204 }
4205
4206 database.logAudit(personalId, 'EMPLOYEE_DELETED', 'employee', employeeId, `Employee deleted from store ${storeId}`, ipAddress);
4207
4208 res.writeHead(200, { 'Content-Type': 'application/json' });
4209 res.end(JSON.stringify({
4210 success: true,
4211 message: 'Employee deleted successfully'
4212 }));
4213 });
4214 }
4215 );
4216 }
4217 );
4218 }
4219 );
4220 }
4221 );
4222 }
4223 );
4224 });
4225 }
4226 );
4227 }
4228 );
4229 }
4230 );
4231 });
4232 }
4233 );
4234 });
4235 }
4236
4237 else if (pathname === '/api/update-employee-status' && req.method === 'POST') {
4238 requireAuth(req, res, (userId) => {
4239 const userIdStr = String(userId);
4240
4241 // Check if this is the admin user
4242 if (userIdStr === '000000') {
4243 res.writeHead(403, { 'Content-Type': 'application/json' });
4244 res.end(JSON.stringify({ success: false, message: 'Only store owners can update employee status' }));
4245 return;
4246 }
4247
4248 if (!userIdStr.startsWith('personal_')) {
4249 res.writeHead(403, { 'Content-Type': 'application/json' });
4250 res.end(JSON.stringify({ success: false, message: 'Only store owners can update employee status' }));
4251 return;
4252 }
4253
4254 const personalId = userIdStr.replace('personal_', '');
4255
4256 database.database.get(
4257 'SELECT boss_id FROM boss WHERE boss_id = $1',
4258 [personalId],
4259 (err, boss) => {
4260 if (err || !boss) {
4261 res.writeHead(403, { 'Content-Type': 'application/json' });
4262 res.end(JSON.stringify({ success: false, message: 'Only store owners can update employee status' }));
4263 return;
4264 }
4265
4266 let body = '';
4267 req.on('data', chunk => {
4268 body += chunk.toString();
4269 });
4270 req.on('end', () => {
4271 const { employeeId, storeId, status } = JSON.parse(body);
4272
4273 if (!employeeId || !storeId || !status) {
4274 res.writeHead(400, { 'Content-Type': 'application/json' });
4275 res.end(JSON.stringify({ success: false, message: 'Employee ID, Store ID and Status are required' }));
4276 return;
4277 }
4278
4279 database.database.get(
4280 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4281 [personalId, storeId],
4282 (err, bossStore) => {
4283 if (err || !bossStore) {
4284 res.writeHead(403, { 'Content-Type': 'application/json' });
4285 res.end(JSON.stringify({ success: false, message: 'You are not authorized to manage employees in this store' }));
4286 return;
4287 }
4288
4289 database.database.get(
4290 'SELECT personal_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4291 [employeeId, storeId],
4292 (err, employeeStore) => {
4293 if (err || !employeeStore) {
4294 res.writeHead(404, { 'Content-Type': 'application/json' });
4295 res.end(JSON.stringify({ success: false, message: 'Employee not found in this store' }));
4296 return;
4297 }
4298
4299 let permissionType = 'EMPLOYEE';
4300 let authorization = 'limited_access';
4301
4302 if (status === 'promoted') {
4303 permissionType = 'MANAGER';
4304 authorization = 'extended_access';
4305 } else if (status === 'suspended') {
4306 permissionType = 'SUSPENDED';
4307 authorization = 'no_access';
4308 } else if (status === 'active') {
4309 permissionType = 'EMPLOYEE';
4310 authorization = 'limited_access';
4311 }
4312
4313 database.database.run(
4314 'UPDATE permissions SET type = $1, authorisation = $2 WHERE personal_id = $3',
4315 [permissionType, authorization, employeeId],
4316 function(err) {
4317 if (err) {
4318 console.error('Error updating employee status:', err);
4319 res.writeHead(500, { 'Content-Type': 'application/json' });
4320 res.end(JSON.stringify({ success: false, message: 'Error updating employee status' }));
4321 return;
4322 }
4323
4324 database.logAudit(personalId, 'EMPLOYEE_STATUS_UPDATED', 'employee', employeeId, `Employee status updated to: ${status}`, ipAddress);
4325
4326 res.writeHead(200, { 'Content-Type': 'application/json' });
4327 res.end(JSON.stringify({
4328 success: true,
4329 message: `Employee status updated to ${status} successfully`
4330 }));
4331 }
4332 );
4333 }
4334 );
4335 }
4336 );
4337 });
4338 }
4339 );
4340 });
4341 }
4342
4343 else if (pathname === '/api/update-employee' && req.method === 'POST') {
4344 requireAuth(req, res, (userId) => {
4345 const userIdStr = String(userId);
4346
4347 // Check if this is the admin user
4348 if (userIdStr === '000000') {
4349 res.writeHead(403, { 'Content-Type': 'application/json' });
4350 res.end(JSON.stringify({ success: false, message: 'Only store owners can update employees' }));
4351 return;
4352 }
4353
4354 if (!userIdStr.startsWith('personal_')) {
4355 res.writeHead(403, { 'Content-Type': 'application/json' });
4356 res.end(JSON.stringify({ success: false, message: 'Only store owners can update employees' }));
4357 return;
4358 }
4359
4360 const personalId = userIdStr.replace('personal_', '');
4361
4362 database.database.get(
4363 'SELECT boss_id FROM boss WHERE boss_id = $1',
4364 [personalId],
4365 (err, boss) => {
4366 if (err || !boss) {
4367 res.writeHead(403, { 'Content-Type': 'application/json' });
4368 res.end(JSON.stringify({ success: false, message: 'Only store owners can update employees' }));
4369 return;
4370 }
4371
4372 let body = '';
4373 req.on('data', chunk => {
4374 body += chunk.toString();
4375 });
4376 req.on('end', () => {
4377 const { employeeId, storeId, firstName, lastName, email } = JSON.parse(body);
4378
4379 if (!employeeId || !storeId) {
4380 res.writeHead(400, { 'Content-Type': 'application/json' });
4381 res.end(JSON.stringify({ success: false, message: 'Employee ID and Store ID are required' }));
4382 return;
4383 }
4384
4385 database.database.get(
4386 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4387 [personalId, storeId],
4388 (err, bossStore) => {
4389 if (err || !bossStore) {
4390 res.writeHead(403, { 'Content-Type': 'application/json' });
4391 res.end(JSON.stringify({ success: false, message: 'You are not authorized to manage employees in this store' }));
4392 return;
4393 }
4394
4395 database.database.get(
4396 'SELECT personal_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4397 [employeeId, storeId],
4398 (err, employeeStore) => {
4399 if (err || !employeeStore) {
4400 res.writeHead(404, { 'Content-Type': 'application/json' });
4401 res.end(JSON.stringify({ success: false, message: 'Employee not found in this store' }));
4402 return;
4403 }
4404
4405 const updates = [];
4406 const params = [];
4407
4408 if (firstName) {
4409 updates.push(`first_name = $${params.length + 1}`);
4410 params.push(firstName);
4411 }
4412
4413 if (lastName) {
4414 updates.push(`last_name = $${params.length + 1}`);
4415 params.push(lastName);
4416 }
4417
4418 if (email) {
4419 if (!validateEmail(email)) {
4420 res.writeHead(400, { 'Content-Type': 'application/json' });
4421 res.end(JSON.stringify({ success: false, message: 'Invalid email format' }));
4422 return;
4423 }
4424 updates.push(`email = $${params.length + 1}`);
4425 params.push(email);
4426 }
4427
4428 if (updates.length === 0) {
4429 res.writeHead(400, { 'Content-Type': 'application/json' });
4430 res.end(JSON.stringify({ success: false, message: 'No fields to update' }));
4431 return;
4432 }
4433
4434 params.push(employeeId);
4435
4436 database.database.run(
4437 `UPDATE personal SET ${updates.join(', ')} WHERE id = $${params.length}`,
4438 params,
4439 function(err) {
4440 if (err) {
4441 console.error('Error updating employee:', err);
4442 res.writeHead(500, { 'Content-Type': 'application/json' });
4443 res.end(JSON.stringify({ success: false, message: 'Error updating employee information' }));
4444 return;
4445 }
4446
4447 // Also update in users table if email was changed
4448 if (email) {
4449 database.database.run(
4450 'UPDATE users SET email = $1 WHERE id = $2',
4451 [email, employeeId],
4452 (err) => {
4453 if (err) {
4454 console.error('Error updating user email:', err);
4455 }
4456 }
4457 );
4458 }
4459
4460 if (firstName || lastName) {
4461 database.database.get(
4462 'SELECT first_name, last_name FROM personal WHERE id = $1',
4463 [employeeId],
4464 (err, personal) => {
4465 if (!err && personal) {
4466 const newUsername = `${personal.first_name} ${personal.last_name}`;
4467 database.database.run(
4468 'UPDATE users SET username = $1 WHERE id = $2',
4469 [newUsername, employeeId],
4470 (err) => {
4471 if (err) {
4472 console.error('Error updating user username:', err);
4473 }
4474 }
4475 );
4476 }
4477 }
4478 );
4479 }
4480
4481 database.logAudit(personalId, 'EMPLOYEE_UPDATED', 'employee', employeeId, `Employee information updated`, ipAddress);
4482
4483 res.writeHead(200, { 'Content-Type': 'application/json' });
4484 res.end(JSON.stringify({
4485 success: true,
4486 message: 'Employee information updated successfully'
4487 }));
4488 }
4489 );
4490 }
4491 );
4492 }
4493 );
4494 });
4495 }
4496 );
4497 });
4498 }
4499
4500 else if (pathname === '/api/store-products' && req.method === 'GET') {
4501 requireStoreOwner()(req, res, (personalId) => {
4502 const storeId = parsedUrl.query.storeId;
4503
4504 if (!storeId) {
4505 database.database.get(
4506 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1',
4507 [personalId],
4508 (err, store) => {
4509 if (err || !store) {
4510 res.writeHead(400, { 'Content-Type': 'application/json' });
4511 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
4512 return;
4513 }
4514
4515 database.getStoreProducts(store.store_id, (err, products) => {
4516 if (err) {
4517 res.writeHead(500, { 'Content-Type': 'application/json' });
4518 res.end(JSON.stringify({ success: false, message: 'Error fetching store products' }));
4519 } else {
4520 res.writeHead(200, { 'Content-Type': 'application/json' });
4521 res.end(JSON.stringify({ success: true, products }));
4522 }
4523 });
4524 }
4525 );
4526
4527 return;
4528 }
4529
4530 database.database.get(
4531 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4532 [personalId, storeId],
4533 (err, ownsStore) => {
4534 if (err || !ownsStore) {
4535 res.writeHead(403, { 'Content-Type': 'application/json' });
4536 res.end(JSON.stringify({ success: false, message: 'You are not authorized to view products in this store' }));
4537 return;
4538 }
4539
4540 database.getStoreProducts(storeId, (err, products) => {
4541 if (err) {
4542 res.writeHead(500, { 'Content-Type': 'application/json' });
4543 res.end(JSON.stringify({ success: false, message: 'Error fetching store products' }));
4544 } else {
4545 res.writeHead(200, { 'Content-Type': 'application/json' });
4546 res.end(JSON.stringify({ success: true, products }));
4547 }
4548 });
4549 }
4550 );
4551 });
4552 }
4553
4554 else if (pathname === '/api/store-orders' && req.method === 'GET') {
4555 requireStoreOwner()(req, res, (personalId) => {
4556 const storeId = parsedUrl.query.storeId;
4557
4558 if (!storeId) {
4559 database.database.get(
4560 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1',
4561 [personalId],
4562 (err, store) => {
4563 if (err || !store) {
4564 res.writeHead(400, { 'Content-Type': 'application/json' });
4565 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
4566 return;
4567 }
4568
4569 database.getStoreOrders(store.store_id, (err, orders) => {
4570 if (err) {
4571 res.writeHead(500, { 'Content-Type': 'application/json' });
4572 res.end(JSON.stringify({ success: false, message: 'Error fetching store orders' }));
4573 } else {
4574 res.writeHead(200, { 'Content-Type': 'application/json' });
4575 res.end(JSON.stringify({ success: true, orders }));
4576 }
4577 });
4578 }
4579 );
4580
4581 return;
4582 }
4583
4584 database.database.get(
4585 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4586 [personalId, storeId],
4587 (err, ownsStore) => {
4588 if (err || !ownsStore) {
4589 res.writeHead(403, { 'Content-Type': 'application/json' });
4590 res.end(JSON.stringify({ success: false, message: 'You are not authorized to view orders in this store' }));
4591 return;
4592 }
4593
4594 database.getStoreOrders(storeId, (err, orders) => {
4595 if (err) {
4596 res.writeHead(500, { 'Content-Type': 'application/json' });
4597 res.end(JSON.stringify({ success: false, message: 'Error fetching store orders' }));
4598 } else {
4599 res.writeHead(200, { 'Content-Type': 'application/json' });
4600 res.end(JSON.stringify({ success: true, orders }));
4601 }
4602 });
4603 }
4604 );
4605 });
4606 }
4607
4608 else if (pathname === '/api/store-employees' && req.method === 'GET') {
4609 requireStoreOwner()(req, res, (personalId) => {
4610 const storeId = parsedUrl.query.storeId;
4611
4612 if (!storeId) {
4613 database.database.get(
4614 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1',
4615 [personalId],
4616 (err, store) => {
4617 if (err || !store) {
4618 res.writeHead(400, { 'Content-Type': 'application/json' });
4619 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
4620 return;
4621 }
4622
4623 database.getStoreEmployees(store.store_id, (err, employees) => {
4624 if (err) {
4625 res.writeHead(500, { 'Content-Type': 'application/json' });
4626 res.end(JSON.stringify({ success: false, message: 'Error fetching store employees' }));
4627 } else {
4628 res.writeHead(200, { 'Content-Type': 'application/json' });
4629 res.end(JSON.stringify({ success: true, employees }));
4630 }
4631 });
4632 }
4633 );
4634
4635 return;
4636 }
4637
4638 database.database.get(
4639 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4640 [personalId, storeId],
4641 (err, ownsStore) => {
4642 if (err || !ownsStore) {
4643 res.writeHead(403, { 'Content-Type': 'application/json' });
4644 res.end(JSON.stringify({ success: false, message: 'You are not authorized to view employees in this store' }));
4645 return;
4646 }
4647
4648 database.getStoreEmployees(storeId, (err, employees) => {
4649 if (err) {
4650 res.writeHead(500, { 'Content-Type': 'application/json' });
4651 res.end(JSON.stringify({ success: false, message: 'Error fetching store employees' }));
4652 } else {
4653 res.writeHead(200, { 'Content-Type': 'application/json' });
4654 res.end(JSON.stringify({ success: true, employees }));
4655 }
4656 });
4657 }
4658 );
4659 });
4660 }
4661
4662 else if (pathname === '/api/store-reports' && req.method === 'GET') {
4663 requireStoreOwner()(req, res, (personalId) => {
4664 const storeId = parsedUrl.query.storeId;
4665
4666 if (!storeId) {
4667 database.database.get(
4668 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1',
4669 [personalId],
4670 (err, store) => {
4671 if (err || !store) {
4672 res.writeHead(400, { 'Content-Type': 'application/json' });
4673 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
4674 return;
4675 }
4676
4677 database.getStoreReports(store.store_id, (err, reports) => {
4678 if (err) {
4679 res.writeHead(500, { 'Content-Type': 'application/json' });
4680 res.end(JSON.stringify({ success: false, message: 'Error fetching store reports' }));
4681 } else {
4682 res.writeHead(200, { 'Content-Type': 'application/json' });
4683 res.end(JSON.stringify({ success: true, reports }));
4684 }
4685 });
4686 }
4687 );
4688
4689 return;
4690 }
4691
4692 database.database.get(
4693 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4694 [personalId, storeId],
4695 (err, ownsStore) => {
4696 if (err || !ownsStore) {
4697 res.writeHead(403, { 'Content-Type': 'application/json' });
4698 res.end(JSON.stringify({ success: false, message: 'You are not authorized to view reports in this store' }));
4699 return;
4700 }
4701
4702 database.getStoreReports(storeId, (err, reports) => {
4703 if (err) {
4704 res.writeHead(500, { 'Content-Type': 'application/json' });
4705 res.end(JSON.stringify({ success: false, message: 'Error fetching store reports' }));
4706 } else {
4707 res.writeHead(200, { 'Content-Type': 'application/json' });
4708 res.end(JSON.stringify({ success: true, reports }));
4709 }
4710 });
4711 }
4712 );
4713 });
4714 }
4715
4716 else if (pathname === '/api/store-stats' && req.method === 'GET') {
4717 requireStoreOwner()(req, res, (personalId) => {
4718 const storeId = parsedUrl.query.storeId;
4719
4720 if (!storeId) {
4721 database.database.get(
4722 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1',
4723 [personalId],
4724 (err, store) => {
4725 if (err || !store) {
4726 res.writeHead(400, { 'Content-Type': 'application/json' });
4727 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
4728 return;
4729 }
4730
4731 database.getStoreStats(store.store_id, (err, stats) => {
4732 if (err) {
4733 res.writeHead(500, { 'Content-Type': 'application/json' });
4734 res.end(JSON.stringify({ success: false, message: 'Error fetching store statistics' }));
4735 } else {
4736 res.writeHead(200, { 'Content-Type': 'application/json' });
4737 res.end(JSON.stringify({ success: true, stats }));
4738 }
4739 });
4740 }
4741 );
4742
4743 return;
4744 }
4745
4746 database.database.get(
4747 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4748 [personalId, storeId],
4749 (err, ownsStore) => {
4750 if (err || !ownsStore) {
4751 res.writeHead(403, { 'Content-Type': 'application/json' });
4752 res.end(JSON.stringify({ success: false, message: 'You are not authorized to view statistics in this store' }));
4753 return;
4754 }
4755
4756 database.getStoreStats(storeId, (err, stats) => {
4757 if (err) {
4758 res.writeHead(500, { 'Content-Type': 'application/json' });
4759 res.end(JSON.stringify({ success: false, message: 'Error fetching store statistics' }));
4760 } else {
4761 res.writeHead(200, { 'Content-Type': 'application/json' });
4762 res.end(JSON.stringify({ success: true, stats }));
4763 }
4764 });
4765 }
4766 );
4767 });
4768 }
4769
4770 else if (pathname === '/api/employee-tasks' && req.method === 'GET') {
4771 requireAuth(req, res, (userId) => {
4772 const userIdStr = String(userId);
4773
4774 // Check if this is the admin user
4775 if (userIdStr === '000000') {
4776 res.writeHead(403, { 'Content-Type': 'application/json' });
4777 res.end(JSON.stringify({ success: false, message: 'Only store employees can access this endpoint' }));
4778 return;
4779 }
4780
4781 if (!userIdStr.startsWith('personal_')) {
4782 res.writeHead(403, { 'Content-Type': 'application/json' });
4783 res.end(JSON.stringify({ success: false, message: 'Only store employees can access this endpoint' }));
4784 return;
4785 }
4786
4787 const personalId = userIdStr.replace('personal_', '');
4788 const storeId = parsedUrl.query.storeId;
4789
4790 if (!storeId) {
4791 res.writeHead(400, { 'Content-Type': 'application/json' });
4792 res.end(JSON.stringify({ success: false, message: 'Store ID is required' }));
4793 return;
4794 }
4795
4796 database.getEmployeeTasks(personalId, storeId, (err, tasks) => {
4797 if (err) {
4798 res.writeHead(500, { 'Content-Type': 'application/json' });
4799 res.end(JSON.stringify({ success: false, message: 'Error fetching employee tasks' }));
4800 } else {
4801 res.writeHead(200, { 'Content-Type': 'application/json' });
4802 res.end(JSON.stringify({ success: true, tasks }));
4803 }
4804 });
4805 });
4806 }
4807
4808 else if (pathname === '/api/client-stats' && req.method === 'GET') {
4809 requireAuth(req, res, (userId) => {
4810 const userIdStr = String(userId);
4811
4812 if (!userIdStr.startsWith('client_')) {
4813 res.writeHead(403, { 'Content-Type': 'application/json' });
4814 res.end(JSON.stringify({ success: false, message: 'Only clients can access this endpoint' }));
4815 return;
4816 }
4817
4818 const clientId = parseInt(userIdStr.replace('client_', ''));
4819
4820 database.getClientStats(clientId, (err, stats) => {
4821 if (err) {
4822 res.writeHead(500, { 'Content-Type': 'application/json' });
4823 res.end(JSON.stringify({ success: false, message: 'Error fetching client statistics' }));
4824 } else {
4825 res.writeHead(200, { 'Content-Type': 'application/json' });
4826 res.end(JSON.stringify({ success: true, stats }));
4827 }
4828 });
4829 });
4830 }
4831
4832 else if (pathname === '/api/delete-product' && req.method === 'POST') {
4833 requireStoreOwner()(req, res, (personalId) => {
4834 let body = '';
4835 req.on('data', chunk => {
4836 body += chunk.toString();
4837 });
4838 req.on('end', () => {
4839 const { productCode, storeId } = JSON.parse(body);
4840
4841 if (!productCode || !storeId) {
4842 res.writeHead(400, { 'Content-Type': 'application/json' });
4843 res.end(JSON.stringify({ success: false, message: 'Product code and store ID are required' }));
4844 return;
4845 }
4846
4847 database.database.get(
4848 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4849 [personalId, storeId],
4850 (err, ownsStore) => {
4851 if (err || !ownsStore) {
4852 res.writeHead(403, { 'Content-Type': 'application/json' });
4853 res.end(JSON.stringify({ success: false, message: 'You are not authorized to delete products from this store' }));
4854 return;
4855 }
4856
4857 database.deleteProduct(productCode, storeId, personalId, (err) => {
4858 if (err) {
4859 console.error('Error deleting product:', err);
4860 res.writeHead(500, { 'Content-Type': 'application/json' });
4861 res.end(JSON.stringify({ success: false, message: 'Error deleting product: ' + err.message }));
4862 } else {
4863 database.logAudit(personalId, 'PRODUCT_DELETED', 'product', productCode, 'Product deleted', ipAddress);
4864 res.writeHead(200, { 'Content-Type': 'application/json' });
4865 res.end(JSON.stringify({ success: true, message: 'Product deleted successfully' }));
4866 }
4867 });
4868 }
4869 );
4870 });
4871 });
4872 }
4873
4874 else if (pathname === '/api/product-by-code' && req.method === 'GET') {
4875 requireAuth(req, res, (userId) => {
4876 const parsedUrl = url.parse(req.url, true);
4877 const productCode = parsedUrl.query.code;
4878
4879 if (!productCode) {
4880 res.writeHead(400, { 'Content-Type': 'application/json' });
4881 res.end(JSON.stringify({ success: false, message: 'Product code is required' }));
4882 return;
4883 }
4884
4885 database.getProductByCode(productCode, (err, product) => {
4886 if (err) {
4887 console.error('Error fetching product:', err);
4888 res.writeHead(500, { 'Content-Type': 'application/json' });
4889 res.end(JSON.stringify({ success: false, message: 'Error fetching product' }));
4890 } else if (!product) {
4891 res.writeHead(404, { 'Content-Type': 'application/json' });
4892 res.end(JSON.stringify({ success: false, message: 'Product not found' }));
4893 } else {
4894 res.writeHead(200, { 'Content-Type': 'application/json' });
4895 res.end(JSON.stringify({ success: true, product }));
4896 }
4897 });
4898 });
4899 }
4900
4901 else if (pathname === '/api/generate-report' && req.method === 'POST') {
4902 requireStoreOwner()(req, res, (personalId) => {
4903 let body = '';
4904 req.on('data', chunk => {
4905 body += chunk.toString();
4906 });
4907 req.on('end', () => {
4908 const { storeId, period, startDate, endDate, type } = JSON.parse(body);
4909
4910 if (!storeId || !period || !startDate || !endDate || !type) {
4911 res.writeHead(400, { 'Content-Type': 'application/json' });
4912 res.end(JSON.stringify({ success: false, message: 'All fields are required' }));
4913 return;
4914 }
4915
4916 database.database.get(
4917 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2',
4918 [personalId, storeId],
4919 (err, ownsStore) => {
4920 if (err || !ownsStore) {
4921 res.writeHead(403, { 'Content-Type': 'application/json' });
4922 res.end(JSON.stringify({ success: false, message: 'You are not authorized to generate reports for this store' }));
4923 return;
4924 }
4925
4926 const reportId = 'RPT' + Date.now().toString().slice(-6);
4927
4928 database.database.run(
4929 'INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES (CURRENT_TIMESTAMP, $1, 0, $2, $3, $4)',
4930 [storeId, period, type, 'Not signed yet'],
4931 function(err) {
4932 if (err) {
4933 console.error('Error generating report:', err);
4934 res.writeHead(500, { 'Content-Type': 'application/json' });
4935 res.end(JSON.stringify({ success: false, message: 'Error generating report: ' + err.message }));
4936 } else {
4937 database.logAudit(personalId, 'REPORT_GENERATED', 'report', reportId, `Report generated: ${type} for ${period}`, ipAddress);
4938
4939 res.writeHead(200, { 'Content-Type': 'application/json' });
4940 res.end(JSON.stringify({
4941 success: true,
4942 message: 'Report generated successfully',
4943 reportId: reportId,
4944 report: {
4945 id: reportId,
4946 storeId: storeId,
4947 period: period,
4948 startDate: startDate,
4949 endDate: endDate,
4950 type: type,
4951 generatedBy: personalId,
4952 generatedAt: new Date().toISOString()
4953 }
4954 }));
4955 }
4956 }
4957 );
4958 }
4959 );
4960 });
4961 });
4962 }
4963
4964 else {
4965 res.writeHead(404, { 'Content-Type': 'text/plain' });
4966 res.end('Page not found');
4967 }
4968});
4969
4970server.listen(port, () => {
4971 console.log(`🎨 Handcraft Marketplace running at http://localhost:${port}`);
4972 console.log('👥 Roles: Admin, Store Owner, Store Employee, Registered Client, Unregistered Guest');
4973 console.log('🎯 Features: Product browsing, ordering, reviews, store management');
4974 console.log('🏪 Store Registration: Available at /register-store.html');
4975 console.log('👤 Client Registration: Available at /register.html');
4976});
Note: See TracBrowser for help on using the repository browser.