source: server.js@ 6c7cfa6

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

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