source: server.js@ 33517cc

finki-main main
Last change on this file since 33517cc was 33517cc, checked in by Klimentina Efremova <klimentina08642@…>, 10 days ago

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